<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  

  
  <title>数据库-基础 | Threadv</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="#  1.1什么是数据库​    数据库：保存数据的仓库。它体现我们电脑中，就是一个文件系统。然后把数据都保存这些特殊的文件中，并且需要使用固定的语言（SQL语言）去操作文件中的数据。 技术定义： ​    数据库(Database)是按照数据结构来组织、[存储和管理数据的建立在计算机存储设备上的仓库。 1.2数据库介">
<meta name="keywords" content="mysql">
<meta property="og:type" content="article">
<meta property="og:title" content="数据库-基础">
<meta property="og:url" content="http://yoursite.com/2019/01/14/mysql/sql_数据库基础/index.html">
<meta property="og:site_name" content="Threadv">
<meta property="og:description" content="#  1.1什么是数据库​    数据库：保存数据的仓库。它体现我们电脑中，就是一个文件系统。然后把数据都保存这些特殊的文件中，并且需要使用固定的语言（SQL语言）去操作文件中的数据。 技术定义： ​    数据库(Database)是按照数据结构来组织、[存储和管理数据的建立在计算机存储设备上的仓库。 1.2数据库介绍​    我们开发应用程序的时候，程序中的所有数据，最后都需要保存到专业软件中">
<meta property="og:updated_time" content="2020-03-29T11:01:38.428Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="数据库-基础">
<meta name="twitter:description" content="#  1.1什么是数据库​    数据库：保存数据的仓库。它体现我们电脑中，就是一个文件系统。然后把数据都保存这些特殊的文件中，并且需要使用固定的语言（SQL语言）去操作文件中的数据。 技术定义： ​    数据库(Database)是按照数据结构来组织、[存储和管理数据的建立在计算机存储设备上的仓库。 1.2数据库介绍​    我们开发应用程序的时候，程序中的所有数据，最后都需要保存到专业软件中">
  
    <link rel="alternate" href="/atom.xml" title="Threadv" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link rel="stylesheet" href="/css/style.css">
</head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/" id="logo">Threadv</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/">Home</a>
        
          <a class="main-nav-link" href="/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="Suche"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="http://yoursite.com"></form>
      </div>
    </div>
  </div>
</header>
      <div class="outer">
        <section id="main"><article id="post-mysql/sql_数据库基础" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2019/01/14/mysql/sql_数据库基础/" class="article-date">
  <time datetime="2019-01-14T02:16:28.000Z" itemprop="datePublished">2019-01-14</time>
</a>
    
  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      数据库-基础
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p># </p>
<h2 id="1-1什么是数据库"><a href="#1-1什么是数据库" class="headerlink" title="1.1什么是数据库"></a>1.1什么是数据库</h2><p>​    数据库：保存数据的仓库。它体现我们电脑中，就是一个文件系统。然后把数据都保存这些特殊的文件中，并且需要使用固定的语言（<strong>SQL语言</strong>）去操作文件中的数据。</p>
<p>技术定义：</p>
<p>​    数据库(Database)是按照<a href="https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84" target="_blank" rel="noopener">数据结构</a>来组织、[<a href="https://baike.baidu.com/item/%E5%AD%98%E5%82%A8" target="_blank" rel="noopener">存储</a>和管理数据的建立在计算机存储设备上的仓库。</p>
<h2 id="1-2数据库介绍"><a href="#1-2数据库介绍" class="headerlink" title="1.2数据库介绍"></a>1.2数据库介绍</h2><p>​    我们开发应用程序的时候，程序中的所有数据，最后都需要保存到专业软件中。这些专业的保存数据的软件我们称为数据库。</p>
<p>我们学习数据库，并不是学习如何去开发一个数据库软件，我们学习的是如何使用数据库以及数据库中的数据记录的操作。而数据库软件是由第三方公司研发。</p>
<h2 id="1-3数据库的分类"><a href="#1-3数据库的分类" class="headerlink" title="1.3数据库的分类"></a>1.3数据库的分类</h2><p>​    关系型、非关系型的数据库</p>
<p>常见的数据库软件：</p>
<p>Oracle：它是Oracle公司的大型关系型数据库，它是收费的。</p>
<p>DB2：IBM公司的数据库，它是收费的。</p>
<p>SqlServer：微软数据库。收费</p>
<p>Sybase：Sybase公司的。 工具PowerDesign 数据库建模工具。</p>
<p>MySql：早期瑞典一个公司发明，后期被sun公司收购，后期被Oracle。</p>
<p>Java开发应用程序主要使用的数据库：</p>
<p>MySQL（5.5）、Oracle、DB2。</p>
<h2 id="1-4什么是关系型数据库"><a href="#1-4什么是关系型数据库" class="headerlink" title="1.4什么是关系型数据库"></a>1.4什么是关系型数据库</h2><p>​    在开发软件的时候，软件中的数据之间必然会有一定的关系存在，需要把这些数据保存在数据库中，同时也要维护数据之间的关系，这时就可以直接使用上述的那些数据库。而上述的所有数据库都属于关系型数据库。</p>
<p>​    描述数据之间的关系，并保存在数据库中，同时学习如果根据这些关系查询数据库中的数据，</p>
<p>关系型数据：设计数据库的时候，需要使用E-R图来描述。实体关系</p>
<p>E-R：实体关系图。</p>
<p>​    实体：可以理解成我们Java程序中的一个对象。在E-R图中使用 矩形(长方形) 表示。</p>
<p>针对一个实体中的属性，我们称为这个实体的数据，在E-R图中使用 椭圆表示。</p>
<p>实体和实体之间的关系：在E-R图中使用菱形表示。</p>
<h1 id="2、mysql在linux-安装篇"><a href="#2、mysql在linux-安装篇" class="headerlink" title="2、mysql在linux-安装篇"></a>2、mysql在linux-安装篇</h1><p>##2.1、vmware中安装linux注意事项</p>
<h3 id="2-1-1、记得关闭防火墙"><a href="#2-1-1、记得关闭防火墙" class="headerlink" title="2.1.1、记得关闭防火墙"></a>2.1.1、记得关闭防火墙</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">service iptables stop</span><br><span class="line">chkconfig iptables off（关闭开机自启：所谓的永久关闭防火墙）</span><br></pre></td></tr></table></figure>
<h3 id="2-1-2、创建统一的管理目录"><a href="#2-1-2、创建统一的管理目录" class="headerlink" title="2.1.2、创建统一的管理目录"></a>2.1.2、创建统一的管理目录</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mkdir -p /exprot/software</span><br><span class="line"></span><br><span class="line">mkdir -p /export/servers</span><br></pre></td></tr></table></figure>
<h3 id="2-1-3软件环境"><a href="#2-1-3软件环境" class="headerlink" title="2.1.3软件环境"></a>2.1.3软件环境</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">VMware、crt、centos6.9</span><br></pre></td></tr></table></figure>
<h3 id="2-1-4安装环境"><a href="#2-1-4安装环境" class="headerlink" title="2.1.4安装环境"></a>2.1.4安装环境</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">1、VMware软件安装</span><br><span class="line"></span><br><span class="line">	2、构建虚拟机</span><br><span class="line"></span><br><span class="line">	3、需要配置Linux（ip,mac地址，hostname，防火墙），就可以通过crt这个客户端连接进行操作</span><br><span class="line"></span><br><span class="line">	4、在linux操作系统进行安装msyql-5.6</span><br><span class="line">	</span><br><span class="line">	说明：因为在linux操作系统中，安装软件的方式主要有3种：1、源码安装（redis）2、rpm安装 		</span><br><span class="line"></span><br><span class="line">3、yum在线安装（安装MySQL为例）---linux联网（）</span><br></pre></td></tr></table></figure>
<h2 id="2-2、centos6-9安装mysql"><a href="#2-2、centos6-9安装mysql" class="headerlink" title="2.2、centos6.9安装mysql"></a>2.2、centos6.9安装mysql</h2><h3 id="2-2-1、检查是否有自带的mysql"><a href="#2-2-1、检查是否有自带的mysql" class="headerlink" title="2.2.1、检查是否有自带的mysql"></a>2.2.1、检查是否有自带的mysql</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">[root@hadoop-01 servers]# rpm -qa |grep mysql </span><br><span class="line">mysql-libs-5.1.73-8.el6_8.x86_64</span><br></pre></td></tr></table></figure>
<h3 id="2-2-2、卸载自带的mysql"><a href="#2-2-2、卸载自带的mysql" class="headerlink" title="2.2.2、卸载自带的mysql"></a>2.2.2、卸载自带的mysql</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">[root@hadoop-01 servers]# rpm -e --nodeps mysql-libs-5.1.73-8.el6_8.x86_64</span><br><span class="line">[root@hadoop-01 servers]#</span><br></pre></td></tr></table></figure>
<h3 id="2-2-3、下载mysql安装包"><a href="#2-2-3、下载mysql安装包" class="headerlink" title="2.2.3、下载mysql安装包"></a>2.2.3、下载mysql安装包</h3><h3 id="2-2-4、上传安装包到linux服务器"><a href="#2-2-4、上传安装包到linux服务器" class="headerlink" title="2.2.4、上传安装包到linux服务器"></a>2.2.4、上传安装包到linux服务器</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">rz 上传文件到指定的目录（yum install lrzsz）</span><br><span class="line">/export/software/mysql</span><br></pre></td></tr></table></figure>
<h3 id="2-2-5、安装"><a href="#2-2-5、安装" class="headerlink" title="2.2.5、安装"></a>2.2.5、安装</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">rpm -ivh *.rpm</span><br></pre></td></tr></table></figure>
<h3 id="2-2-6、查看初始化密码"><a href="#2-2-6、查看初始化密码" class="headerlink" title="2.2.6、查看初始化密码"></a>2.2.6、查看初始化密码</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !</span><br><span class="line">You will find that password in &apos;/root/.mysql_secret&apos;.</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql ~]# cat /root/.mysql_secret</span><br><span class="line"># The random password set for the root user at Wed Aug  8 22:19:00 2018 (local time): xQkcU3kbyuZby1_V</span><br><span class="line"></span><br><span class="line">[root@mysql ~]#</span><br></pre></td></tr></table></figure>
<h3 id="2-2-7、启动mysql并登录"><a href="#2-2-7、启动mysql并登录" class="headerlink" title="2.2.7、启动mysql并登录"></a>2.2.7、启动mysql并登录</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#启动mysql</span><br><span class="line">service mysql start</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">#登录mysql</span><br><span class="line">mysql -uroot -p</span><br><span class="line">(粘贴密码：xQkcU3kbyuZby1_V)</span><br></pre></td></tr></table></figure>
<h3 id="2-2-8、修改密码"><a href="#2-2-8、修改密码" class="headerlink" title="2.2.8、修改密码"></a>2.2.8、修改密码</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">set PASSWORD=PASSWORD(&apos;123456&apos;);</span><br></pre></td></tr></table></figure>
<h3 id="2-2-9、退出mysql客户端"><a href="#2-2-9、退出mysql客户端" class="headerlink" title="2.2.9、退出mysql客户端"></a>2.2.9、退出mysql客户端</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;quit</span><br></pre></td></tr></table></figure>
<h3 id="2-2-10、用新密码进行登录"><a href="#2-2-10、用新密码进行登录" class="headerlink" title="2.2.10、用新密码进行登录"></a>2.2.10、用新密码进行登录</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql -uroot -p</span><br><span class="line">123456（新密码）</span><br></pre></td></tr></table></figure>
<h3 id="2-2-11、远程授权"><a href="#2-2-11、远程授权" class="headerlink" title="2.2.11、远程授权"></a>2.2.11、远程授权</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">grant all privileges on *.* to &apos;root&apos; @&apos;%&apos; identified by &apos;123456&apos;; </span><br><span class="line">flush privileges;</span><br></pre></td></tr></table></figure>
<h3 id="2-2-12、验证远程授权是否成功"><a href="#2-2-12、验证远程授权是否成功" class="headerlink" title="2.2.12、验证远程授权是否成功"></a>2.2.12、验证远程授权是否成功</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">通过windows的mysql客户端工具连接，是否能连接上，能连接上就授权成功，没有连接上，说明没有授权成功！</span><br></pre></td></tr></table></figure>
<h1 id="3、mysql-基础操作篇"><a href="#3、mysql-基础操作篇" class="headerlink" title="3、mysql-基础操作篇"></a>3、mysql-基础操作篇</h1><h2 id="3-1、登录mysql"><a href="#3-1、登录mysql" class="headerlink" title="3.1、登录mysql"></a>3.1、登录mysql</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql -uroot -p</span><br><span class="line">123456</span><br></pre></td></tr></table></figure>
<h2 id="3-2、退出mysql"><a href="#3-2、退出mysql" class="headerlink" title="3.2、退出mysql"></a>3.2、退出mysql</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;quit</span><br></pre></td></tr></table></figure>
<h2 id="3-3、输入查询"><a href="#3-3、输入查询" class="headerlink" title="3.3、输入查询"></a>3.3、输入查询</h2><ul>
<li><p>查看当前mysql的版本号及当前时间</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT VERSION(), CURRENT_DATE;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT VERSION(), CURRENT_DATE;</span><br><span class="line">+-----------+--------------+</span><br><span class="line">| VERSION() | CURRENT_DATE |</span><br><span class="line">+-----------+--------------+</span><br><span class="line">| 5.6.25    | 2018-08-08   |</span><br><span class="line">+-----------+--------------+</span><br><span class="line">1 row in set (0.32 sec)</span><br></pre></td></tr></table></figure>
</li>
<li><p>mysql中sql语句不区分大小写</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT VERSION(), CURRENT_DATE;</span><br><span class="line">mysql&gt; select version(), current_date;</span><br><span class="line">mysql&gt; SeLeCt vErSiOn(), current_DATE;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT VERSION(), CURRENT_DATE;</span><br><span class="line">+-----------+--------------+</span><br><span class="line">| VERSION() | CURRENT_DATE |</span><br><span class="line">+-----------+--------------+</span><br><span class="line">| 5.6.25    | 2018-08-08   |</span><br><span class="line">+-----------+--------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select version(), current_date;</span><br><span class="line">+-----------+--------------+</span><br><span class="line">| version() | current_date |</span><br><span class="line">+-----------+--------------+</span><br><span class="line">| 5.6.25    | 2018-08-08   |</span><br><span class="line">+-----------+--------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; SeLeCt vErSiOn(), current_DATE;</span><br><span class="line">+-----------+--------------+</span><br><span class="line">| vErSiOn() | current_DATE |</span><br><span class="line">+-----------+--------------+</span><br><span class="line">| 5.6.25    | 2018-08-08   |</span><br><span class="line">+-----------+--------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt;</span><br></pre></td></tr></table></figure>
</li>
<li><p>可以进行简单的计算（如下所示）</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;SELECT SIN(PI()/4), (4+1)*5;</span><br><span class="line"></span><br><span class="line">mysql&gt; SELECT SIN(PI()/4), (4+1)*5;</span><br><span class="line">+--------------------+---------+</span><br><span class="line">| SIN(PI()/4)        | (4+1)*5 |</span><br><span class="line">+--------------------+---------+</span><br><span class="line">| 0.7071067811865475 |      25 |</span><br><span class="line">+--------------------+---------+</span><br><span class="line">1 row in set (0.34 sec)</span><br></pre></td></tr></table></figure>
</li>
<li><p>多条语句比较短，可以写在一行</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;SELECT VERSION(); SELECT NOW();</span><br><span class="line"></span><br><span class="line">mysql&gt; SELECT VERSION(); SELECT NOW();</span><br><span class="line">+-----------+</span><br><span class="line">| VERSION() |</span><br><span class="line">+-----------+</span><br><span class="line">| 5.6.25    |</span><br><span class="line">+-----------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">+---------------------+</span><br><span class="line">| NOW()               |</span><br><span class="line">+---------------------+</span><br><span class="line">| 2018-08-08 23:11:11 |</span><br><span class="line">+---------------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br></pre></td></tr></table></figure>
</li>
<li><p>多个字段之间可以用逗号分隔，多行组成一条语句结束以分号结束</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT</span><br><span class="line">-&gt; USER()</span><br><span class="line">-&gt; ,</span><br><span class="line">-&gt; CURRENT_DATE;</span><br><span class="line">+---------------+--------------+</span><br><span class="line">| USER() | CURRENT_DATE |</span><br><span class="line">+---------------+--------------+</span><br><span class="line">| jon@localhost | 2010-08-06 |</span><br><span class="line">+---------------+--------------+</span><br></pre></td></tr></table></figure>
</li>
<li><p>sql语句写了一半，又不想执行可以在语句末尾加上’\c’</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select </span><br><span class="line">    -&gt; user()</span><br><span class="line">    -&gt; \c</span><br><span class="line">mysql&gt;</span><br></pre></td></tr></table></figure>
</li>
</ul>
<p>##3.4、创建和使用数据库</p>
<ul>
<li><p>查看当前有哪些数据库</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;show databases;</span><br><span class="line"></span><br><span class="line">mysql&gt; show databases;</span><br><span class="line">+--------------------+</span><br><span class="line">| Database           |</span><br><span class="line">+--------------------+</span><br><span class="line">| information_schema |</span><br><span class="line">| mysql              |</span><br><span class="line">| performance_schema |</span><br><span class="line">| test               |</span><br><span class="line">+--------------------+</span><br><span class="line">4 rows in set (0.07 sec)</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建数据库</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; CREATE DATABASE menagerie;</span><br></pre></td></tr></table></figure>
</li>
<li><p>使用及切换数据库</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; USE menagerie</span><br><span class="line">Database changed</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h2 id="3-5、创建表及使用"><a href="#3-5、创建表及使用" class="headerlink" title="3.5、创建表及使用"></a>3.5、创建表及使用</h2><ul>
<li><p>查看当前数据库有哪些表</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;show tables;</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建一个表</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),</span><br><span class="line">    -&gt; species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);</span><br></pre></td></tr></table></figure>
</li>
<li><p>校验创建表语句是否和执行的一致</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;show create table pet;</span><br><span class="line"></span><br><span class="line">+-------+--------------------------------</span><br><span class="line">| Table | Create Table                   </span><br><span class="line">+-------+--------------------------------</span><br><span class="line">| pet   | CREATE TABLE `pet` (</span><br><span class="line">  `name` varchar(20) DEFAULT NULL,</span><br><span class="line">  `owner` varchar(20) DEFAULT NULL,</span><br><span class="line">  `species` varchar(20) DEFAULT NULL,</span><br><span class="line">  `sex` char(1) DEFAULT NULL,</span><br><span class="line">  `birth` date DEFAULT NULL,</span><br><span class="line">  `death` date DEFAULT NULL</span><br><span class="line">) ENGINE=InnoDB DEFAULT CHARSET=latin1 |</span><br><span class="line">+-------+--------------------------------</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看表详情</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; desc pet;</span><br><span class="line">+---------+-------------+------+-----+---------+-------+</span><br><span class="line">| Field   | Type        | Null | Key | Default | Extra |</span><br><span class="line">+---------+-------------+------+-----+---------+-------+</span><br><span class="line">| name    | varchar(20) | YES  |     | NULL    |       |</span><br><span class="line">| owner   | varchar(20) | YES  |     | NULL    |       |</span><br><span class="line">| species | varchar(20) | YES  |     | NULL    |       |</span><br><span class="line">| sex     | char(1)     | YES  |     | NULL    |       |</span><br><span class="line">| birth   | date        | YES  |     | NULL    |       |</span><br><span class="line">| death   | date        | YES  |     | NULL    |       |</span><br><span class="line">+---------+-------------+------+-----+---------+-------+</span><br></pre></td></tr></table></figure>
</li>
<li><p>准备数据</p>
</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">Fluffy Harold cat f 1993-02-04</span><br><span class="line">Claws Gwen cat m 1994-03-17</span><br><span class="line">Buffy Harold dog f 1989-05-13</span><br><span class="line">Fang Benny dog m 1990-08-27</span><br><span class="line">Bowser Diane dog m 1979-08-31 1995-07-29</span><br><span class="line">Chirpy Gwen bird f 1998-09-11</span><br><span class="line">Whistler Gwen bird 1997-12-09</span><br><span class="line">Slim Benny snake m 1996-04-29</span><br></pre></td></tr></table></figure>
<h2 id="3-6、表中导入数据"><a href="#3-6、表中导入数据" class="headerlink" title="3.6、表中导入数据"></a>3.6、表中导入数据</h2><p>​    在表中导入数据的方式有两种</p>
<ul>
<li><p>第一种：将以上数据整理成SQL语句，insert into pet….</p>
</li>
<li><p>第二种：通过加载文件的方式将数据导入到表中</p>
<p>1、创建一个pet.txt的文件（注：每个字段中用tab键隔开，字段没有值得记录用\N代替）</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">Fluffy  Harold  cat     f       1993-02-04</span><br><span class="line">Claws   Gwen    cat     m       1994-03-17</span><br><span class="line">Buffy   Harold  dog     f       1989-05-13</span><br><span class="line">Fang    Benny   dog     m       1990-08-27</span><br><span class="line">Bowser  Diane   dog     m       1979-08-31      1995-07-29</span><br><span class="line">Chirpy  Gwen    bird    f       1998-09-11</span><br><span class="line">Whistler        Gwen    bird    \N      1997-12-09      \N</span><br><span class="line">Slim    Benny   snake   m       1996-04-29</span><br></pre></td></tr></table></figure>
<p>2、加载数据</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; load data local infile &apos;/root/data/pet.txt&apos; into table pet;</span><br><span class="line">Query OK, 8 rows affected, 6 warnings (0.06 sec)</span><br><span class="line">Records: 8  Deleted: 0  Skipped: 0  Warnings: 6</span><br></pre></td></tr></table></figure>
<p>3、校验是否加载进去</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select *from pet;</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br><span class="line">| name     | owner  | species | sex  | birth      | death      |</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br><span class="line">| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |</span><br><span class="line">| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |</span><br><span class="line">| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |</span><br><span class="line">| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |</span><br><span class="line">| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |</span><br><span class="line">| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |</span><br><span class="line">| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |</span><br><span class="line">| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br><span class="line">8 rows in set (0.01 sec)</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h2 id="3-7、数据检索部分"><a href="#3-7、数据检索部分" class="headerlink" title="3.7、数据检索部分"></a>3.7、数据检索部分</h2><h3 id="3-7-1、检索全部数据"><a href="#3-7-1、检索全部数据" class="headerlink" title="3.7.1、检索全部数据"></a>3.7.1、检索全部数据</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select *from pet;</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br><span class="line">| name     | owner  | species | sex  | birth      | death      |</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br><span class="line">| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |</span><br><span class="line">| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |</span><br><span class="line">| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |</span><br><span class="line">| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |</span><br><span class="line">| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |</span><br><span class="line">| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |</span><br><span class="line">| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |</span><br><span class="line">| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br><span class="line">8 rows in set (0.01 sec)</span><br></pre></td></tr></table></figure>
<h3 id="3-7-2、删除表中全部数据"><a href="#3-7-2、删除表中全部数据" class="headerlink" title="3.7.2、删除表中全部数据"></a>3.7.2、删除表中全部数据</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; DELETE FROM pet;</span><br><span class="line">mysql&gt; LOAD DATA LOCAL INFILE &apos;/path/pet.txt&apos; INTO TABLE pet;</span><br></pre></td></tr></table></figure>
<h3 id="3-7-3、更新表中特定记录的数据"><a href="#3-7-3、更新表中特定记录的数据" class="headerlink" title="3.7.3、更新表中特定记录的数据"></a>3.7.3、更新表中特定记录的数据</h3><ul>
<li>更新表中名字为Bowser的生日</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; UPDATE pet SET birth = &apos;1989-08-31&apos; WHERE name = &apos;Bowser&apos;;</span><br></pre></td></tr></table></figure>
<h3 id="3-7-4、查询特定的行"><a href="#3-7-4、查询特定的行" class="headerlink" title="3.7.4、查询特定的行"></a>3.7.4、查询特定的行</h3><ul>
<li>查询名字为Bowser的记录</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT * FROM pet WHERE name = &apos;Bowser&apos;;</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br><span class="line">| name | owner | species | sex | birth | death |</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br><span class="line">| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br></pre></td></tr></table></figure>
<p>说明：字符串比较不区分大小写！如下所示：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT * FROM pet WHERE name = &apos;Bowser&apos;;</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br><span class="line">| name   | owner | species | sex  | birth      | death      |</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br><span class="line">| Bowser | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; SELECT * FROM pet WHERE name = &apos;BowsEr&apos;;</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br><span class="line">| name   | owner | species | sex  | birth      | death      |</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br><span class="line">| Bowser | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; SELECT * FROM pet WHERE name = &apos;BOWSER&apos;;</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br><span class="line">| name   | owner | species | sex  | birth      | death      |</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br><span class="line">| Bowser | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |</span><br><span class="line">+--------+-------+---------+------+------------+------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<h4 id="3-7-4-1、查找生日在1998年以后的特定查询"><a href="#3-7-4-1、查找生日在1998年以后的特定查询" class="headerlink" title="3.7.4.1、查找生日在1998年以后的特定查询"></a>3.7.4.1、查找生日在1998年以后的特定查询</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT * FROM pet WHERE birth &gt;= &apos;1998-1-1&apos;;</span><br><span class="line">+----------+-------+---------+------+------------+-------+</span><br><span class="line">| name | owner | species | sex | birth | death |</span><br><span class="line">+----------+-------+---------+------+------------+-------+</span><br><span class="line">| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |</span><br><span class="line">| Puffball | Diane | hamster | f | 1999-03-30 | NULL |</span><br><span class="line">+----------+-------+---------+------+------------+-------+</span><br></pre></td></tr></table></figure>
<h4 id="3-7-4-2、多条件查询（and-or）"><a href="#3-7-4-2、多条件查询（and-or）" class="headerlink" title="3.7.4.2、多条件查询（and | or）"></a>3.7.4.2、多条件查询（and | or）</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT * FROM pet WHERE species = &apos;dog&apos; AND sex = &apos;f&apos;;</span><br><span class="line">+-------+--------+---------+------+------------+-------+</span><br><span class="line">| name | owner | species | sex | birth | death |</span><br><span class="line">+-------+--------+---------+------+------------+-------+</span><br><span class="line">| Buffy | Harold | dog | f | 1989-05-13 | NULL |</span><br><span class="line">+-------+--------+---------+------+------------+-------+</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT * FROM pet WHERE species = &apos;snake&apos; OR species = &apos;bird&apos;;</span><br><span class="line">+----------+-------+---------+------+------------+-------+</span><br><span class="line">| name | owner | species | sex | birth | death |</span><br><span class="line">+----------+-------+---------+------+------------+-------+</span><br><span class="line">| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |</span><br><span class="line">| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |</span><br><span class="line">| Slim | Benny | snake | m | 1996-04-29 | NULL |</span><br><span class="line">+----------+-------+---------+------+------------+-------+</span><br></pre></td></tr></table></figure>
<ul>
<li><p>优先执行括号中的逻辑</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT * FROM pet WHERE (species = &apos;cat&apos; AND sex = &apos;m&apos;)</span><br><span class="line">-&gt; OR (species = &apos;dog&apos; AND sex = &apos;f&apos;);</span><br><span class="line">+-------+--------+---------+------+------------+-------+</span><br><span class="line">| name | owner | species | sex | birth | death |</span><br><span class="line">+-------+--------+---------+------+------------+-------+</span><br><span class="line">| Claws | Gwen | cat | m | 1994-03-17 | NULL |</span><br><span class="line">| Buffy | Harold | dog | f | 1989-05-13 | NULL |</span><br><span class="line">+-------+--------+---------+------+------------+-------+</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h3 id="3-7-5、检索特定的列"><a href="#3-7-5、检索特定的列" class="headerlink" title="3.7.5、检索特定的列"></a>3.7.5、检索特定的列</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT name, birth FROM pet;</span><br><span class="line">+----------+------------+</span><br><span class="line">| name | birth |</span><br><span class="line">+----------+------------+</span><br><span class="line">| Fluffy | 1993-02-04 |</span><br><span class="line">| Claws | 1994-03-17 |</span><br><span class="line">| Buffy | 1989-05-13 |</span><br><span class="line">| Fang | 1990-08-27 |</span><br><span class="line">| Bowser | 1989-08-31 |</span><br><span class="line">| Chirpy | 1998-09-11 |</span><br><span class="line">| Whistler | 1997-12-09 |</span><br><span class="line">| Slim | 1996-04-29 |</span><br><span class="line">| Puffball | 1999-03-30 |</span><br><span class="line">+----------+------------+</span><br></pre></td></tr></table></figure>
<ul>
<li><p>查询不重复的字段要使用关键词DISTINCT</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT DISTINCT owner FROM pet;</span><br><span class="line">+--------+</span><br><span class="line">| owner |</span><br><span class="line">+--------+</span><br><span class="line">| Benny |</span><br><span class="line">| Diane |</span><br><span class="line">| Gwen |</span><br><span class="line">| Harold |</span><br><span class="line">+--------+</span><br></pre></td></tr></table></figure>
</li>
<li><p>可以使用组合条件查询特定的列</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT name, species, birth FROM pet</span><br><span class="line">-&gt; WHERE species = &apos;dog&apos; OR species = &apos;cat&apos;;</span><br><span class="line">+--------+---------+------------+</span><br><span class="line">| name | species | birth |</span><br><span class="line">+--------+---------+------------+</span><br><span class="line">| Fluffy | cat | 1993-02-04 |</span><br><span class="line">| Claws | cat | 1994-03-17 |</span><br><span class="line">| Buffy | dog | 1989-05-13 |</span><br><span class="line">| Fang | dog | 1990-08-27 |</span><br><span class="line">| Bowser | dog | 1989-08-31 |</span><br><span class="line">+--------+---------+------------+</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h3 id="3-7-6、排序"><a href="#3-7-6、排序" class="headerlink" title="3.7.6、排序"></a>3.7.6、排序</h3><ul>
<li>根据某个字段进行排序（关键词：ORDER BY ）</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT name, birth FROM pet ORDER BY birth;</span><br><span class="line">+----------+------------+</span><br><span class="line">| name | birth |</span><br><span class="line">+----------+------------+</span><br><span class="line">| Buffy | 1989-05-13 |</span><br><span class="line">| Bowser | 1989-08-31 |</span><br><span class="line">| Fang | 1990-08-27 |</span><br><span class="line">| Fluffy | 1993-02-04 |</span><br><span class="line">| Claws | 1994-03-17 |</span><br><span class="line">| Slim | 1996-04-29 |</span><br><span class="line">| Whistler | 1997-12-09 |</span><br><span class="line">| Chirpy | 1998-09-11 |</span><br><span class="line">| Puffball | 1999-03-30 |</span><br><span class="line">+----------+------------+</span><br></pre></td></tr></table></figure>
<ul>
<li><p>升降序排列（desc：降序；asc：升序）</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT name, birth FROM pet ORDER BY birth desc;//降序排列</span><br><span class="line">mysql&gt; SELECT name, birth FROM pet ORDER BY birth asc ;//升序排列</span><br></pre></td></tr></table></figure>
</li>
<li><p>多列排序</p>
<p>根据species字段升序排列，根据birth字段降序排列</p>
<p><strong>注： ORDER BY species 中无asc，desc，默认为升序排列</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT name, species, birth FROM pet</span><br><span class="line">-&gt; ORDER BY species, birth DESC;</span><br><span class="line">+----------+---------+------------+</span><br><span class="line">| name | species | birth |</span><br><span class="line">+----------+---------+------------+</span><br><span class="line">| Chirpy | bird | 1998-09-11 |</span><br><span class="line">| Whistler | bird | 1997-12-09 |</span><br><span class="line">| Claws | cat | 1994-03-17 |</span><br><span class="line">| Fluffy | cat | 1993-02-04 |</span><br><span class="line">| Fang | dog | 1990-08-27 |</span><br><span class="line">| Bowser | dog | 1989-08-31 |</span><br><span class="line">| Buffy | dog | 1989-05-13 |</span><br><span class="line">| Puffball | hamster | 1999-03-30 |</span><br><span class="line">| Slim | snake | 1996-04-29 |</span><br><span class="line">+----------+---------+------------+</span><br></pre></td></tr></table></figure>
</li>
</ul>
<h3 id="3-7-7、日期计算"><a href="#3-7-7、日期计算" class="headerlink" title="3.7.7、日期计算"></a>3.7.7、日期计算</h3><p>查看宠物多少岁，就可以使用计算日期的函数TIMESTAMPDIFF()</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">#查询当前的日期</span><br><span class="line">mysql&gt; select curdate() from pet;</span><br><span class="line">+------------+</span><br><span class="line">| curdate()  |</span><br><span class="line">+------------+</span><br><span class="line">| 2018-08-09 |</span><br><span class="line">+------------+</span><br><span class="line"></span><br><span class="line">#获取当年的年</span><br><span class="line">mysql&gt; select YEAR(&apos;2018-02-05&apos;) AS YEARS from pet;</span><br><span class="line">+-------+</span><br><span class="line">| YEARS |</span><br><span class="line">+-------+</span><br><span class="line">|  2018 |</span><br><span class="line">+-------+</span><br><span class="line"></span><br><span class="line">#获取当年的月</span><br><span class="line">mysql&gt; select month(&apos;2018-02-05&apos;) AS YEARS from pet;      </span><br><span class="line">+-------+</span><br><span class="line">| YEARS |</span><br><span class="line">+-------+</span><br><span class="line">|     2 |</span><br><span class="line">+-------+</span><br><span class="line"></span><br><span class="line">#获取当年的日</span><br><span class="line">mysql&gt; select day(&apos;2018-02-05&apos;) AS YEARS from pet;      </span><br><span class="line">+-------+</span><br><span class="line">| YEARS |</span><br><span class="line">+-------+</span><br><span class="line">|     5 |</span><br><span class="line">+-------+</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT name, birth, CURDATE(),</span><br><span class="line">-&gt; TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age</span><br><span class="line">-&gt; FROM pet;</span><br></pre></td></tr></table></figure>
<h3 id="3-7-8、null和not-null值"><a href="#3-7-8、null和not-null值" class="headerlink" title="3.7.8、null和not null值"></a>3.7.8、null和not null值</h3><p>对一些字段类型要进行检查，判断某些字段是否为NULL，或者 non-NULL</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT name, birth, death,</span><br><span class="line">-&gt; TIMESTAMPDIFF(YEAR,birth,death) AS age</span><br><span class="line">-&gt; FROM pet WHERE death IS NOT NULL ORDER BY age;</span><br><span class="line">+--------+------------+------------+------+</span><br><span class="line">| name | birth | death | age |</span><br><span class="line">+--------+------------+------------+------+</span><br><span class="line">| Bowser | 1989-08-31 | 1995-07-29 | 5 |</span><br><span class="line">+--------+------------+------------+------+</span><br></pre></td></tr></table></figure>
<h1 id="4、实例"><a href="#4、实例" class="headerlink" title="4、实例"></a>4、实例</h1><p>以下是如何解决MySQL的一些常见问题的示例。</p>
<h2 id="4-1、首先创建一个表，并且导入数据"><a href="#4-1、首先创建一个表，并且导入数据" class="headerlink" title="4.1、首先创建一个表，并且导入数据"></a>4.1、首先创建一个表，并且导入数据</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE shop (</span><br><span class="line">article INT(4) UNSIGNED ZEROFILL DEFAULT &apos;0000&apos; NOT NULL,</span><br><span class="line">dealer CHAR(20) DEFAULT &apos;&apos; NOT NULL,</span><br><span class="line">price DOUBLE(16,2) DEFAULT &apos;0.00&apos; NOT NULL,</span><br><span class="line">PRIMARY KEY(article, dealer));</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">INSERT INTO shop VALUES</span><br><span class="line">(1,&apos;A&apos;,3.45),(1,&apos;B&apos;,3.99),(2,&apos;A&apos;,10.99),(3,&apos;B&apos;,1.45),</span><br><span class="line">(3,&apos;C&apos;,1.69),(3,&apos;D&apos;,1.25),(4,&apos;D&apos;,19.95);</span><br></pre></td></tr></table></figure>
<h2 id="4-2、检索表中的全部数据"><a href="#4-2、检索表中的全部数据" class="headerlink" title="4.2、检索表中的全部数据"></a>4.2、检索表中的全部数据</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">select * from shop;</span><br><span class="line"></span><br><span class="line">+---------+--------+-------+</span><br><span class="line">| article | dealer | price |</span><br><span class="line">+---------+--------+-------+</span><br><span class="line">|    0001 | A      |  3.45 |</span><br><span class="line">|    0001 | B      |  3.99 |</span><br><span class="line">|    0002 | A      | 10.99 |</span><br><span class="line">|    0003 | B      |  1.45 |</span><br><span class="line">|    0003 | C      |  1.69 |</span><br><span class="line">|    0003 | D      |  1.25 |</span><br><span class="line">|    0004 | D      | 19.95 |</span><br><span class="line">+---------+--------+-------+</span><br></pre></td></tr></table></figure>
<h2 id="4-3、求某一列的最大值或者-最小值"><a href="#4-3、求某一列的最大值或者-最小值" class="headerlink" title="4.3、求某一列的最大值或者 最小值"></a>4.3、求某一列的最大值或者 最小值</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">SELECT MAX(article) AS article FROM shop;</span><br><span class="line"></span><br><span class="line">+---------+</span><br><span class="line">| article |</span><br><span class="line">+---------+</span><br><span class="line">|       4 |</span><br><span class="line">+---------+</span><br><span class="line"></span><br><span class="line">//求某一列的最小值</span><br><span class="line"> select min(price) as article from shop; </span><br><span class="line"> </span><br><span class="line"> +---------+</span><br><span class="line">| article |</span><br><span class="line">+---------+</span><br><span class="line">|    1.25 |</span><br><span class="line">+---------+</span><br></pre></td></tr></table></figure>
<p>##4.4、过滤出某个字段值最大的整条记录数据-涉及到子查询</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SELECT article, dealer, price</span><br><span class="line">FROM shop</span><br><span class="line">WHERE price=(SELECT MAX(price) FROM shop);</span><br><span class="line"></span><br><span class="line">+---------+--------+-------+</span><br><span class="line">| article | dealer | price |</span><br><span class="line">+---------+--------+-------+</span><br><span class="line">|    0004 | D      | 19.95 |</span><br><span class="line">+---------+--------+-------+</span><br></pre></td></tr></table></figure>
<h2 id="4-4、也可以通过关联查询来进行检索"><a href="#4-4、也可以通过关联查询来进行检索" class="headerlink" title="4.4、也可以通过关联查询来进行检索"></a>4.4、也可以通过关联查询来进行检索</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SELECT s1.article, s1.dealer, s1.price</span><br><span class="line">FROM shop s1</span><br><span class="line">LEFT JOIN shop s2 ON s1.price &lt; s2.price</span><br><span class="line">WHERE s2.article IS NULL;</span><br><span class="line"></span><br><span class="line">SELECT article, dealer, price</span><br><span class="line">FROM shop</span><br><span class="line">ORDER BY price DESC</span><br><span class="line">LIMIT 1;</span><br></pre></td></tr></table></figure>
<h2 id="4-5、求出每一列的最大值，并且根据某一个字段进行分组–分组topn求法"><a href="#4-5、求出每一列的最大值，并且根据某一个字段进行分组–分组topn求法" class="headerlink" title="4.5、求出每一列的最大值，并且根据某一个字段进行分组–分组topn求法"></a>4.5、求出每一列的最大值，并且根据某一个字段进行分组–分组topn求法</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">SELECT article, MAX(price) AS price</span><br><span class="line">FROM shop</span><br><span class="line">GROUP BY article;</span><br><span class="line"></span><br><span class="line">+---------+-------+</span><br><span class="line">| article | price |</span><br><span class="line">+---------+-------+</span><br><span class="line">|    0001 |  3.99 |</span><br><span class="line">|    0002 | 10.99 |</span><br><span class="line">|    0003 |  1.69 |</span><br><span class="line">|    0004 | 19.95 |</span><br><span class="line">+---------+-------+</span><br></pre></td></tr></table></figure>
<p>4.5的另一种写法</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">SELECT article, dealer, price</span><br><span class="line">FROM shop s1</span><br><span class="line">WHERE price=(SELECT MAX(s2.price)</span><br><span class="line">FROM shop s2</span><br><span class="line">WHERE s1.article = s2.article);</span><br><span class="line"></span><br><span class="line">+---------+--------+-------+</span><br><span class="line">| article | dealer | price |</span><br><span class="line">+---------+--------+-------+</span><br><span class="line">|    0001 | B      |  3.99 |</span><br><span class="line">|    0002 | A      | 10.99 |</span><br><span class="line">|    0003 | C      |  1.69 |</span><br><span class="line">|    0004 | D      | 19.95 |</span><br><span class="line">+---------+--------+-------+</span><br></pre></td></tr></table></figure>
<h1 id="5、SQL中的聚合函数"><a href="#5、SQL中的聚合函数" class="headerlink" title="5、SQL中的聚合函数"></a>5、SQL中的聚合函数</h1><p>​    SQL语言中定义了部分的函数，可以帮助我们完成对查询结果的计算操作：</p>
<p>1.count 统计个数（行数）</p>
<p>2.sum函数：求和</p>
<p>3.avg函数：求平均值</p>
<p>4.max、min 求最大值和最小值</p>
<h2 id="5-1、count函数"><a href="#5-1、count函数" class="headerlink" title="5.1、count函数"></a>5.1、count函数</h2><p><strong>语法：select count(*)|count(</strong>列名) from<strong>表名</strong></p>
<p>注意： count在根据指定的列统计的时候，如果这一列中有null 不会被统计在其中。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from pet;</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br><span class="line">| name     | owner  | species | sex  | birth      | death      |</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br><span class="line">| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |</span><br><span class="line">| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |</span><br><span class="line">| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |</span><br><span class="line">| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |</span><br><span class="line">| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |</span><br><span class="line">| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |</span><br><span class="line">| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |</span><br><span class="line">| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br><span class="line">8 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select count(sex) from pet; </span><br><span class="line">+------------+</span><br><span class="line">| count(sex) |</span><br><span class="line">+------------+</span><br><span class="line">|          7 |</span><br><span class="line">+------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select count(owner) from pet;   </span><br><span class="line">+--------------+</span><br><span class="line">| count(owner) |</span><br><span class="line">+--------------+</span><br><span class="line">|            8 |</span><br><span class="line">+--------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select count(death) from pet;     </span><br><span class="line">+--------------+</span><br><span class="line">| count(death) |</span><br><span class="line">+--------------+</span><br><span class="line">|            1 |</span><br><span class="line">+--------------+</span><br></pre></td></tr></table></figure>
<h2 id="5-2、sum函数"><a href="#5-2、sum函数" class="headerlink" title="5.2、sum函数"></a>5.2、sum函数</h2><p><strong>语法：select sum(</strong>列名) from 表名; </p>
<p>注意事项：</p>
<p>1、如果使用sum 多列进行求和的时候，如果某一列中的有null，这一列所在的行中的其他数据不会被加到总和。</p>
<p>2、可以使用mysql 数据库提供的函数 ifnull(列名,值)</p>
<p>3、在数据库中定义double类型数据，是一个近似值，需要确定准确的位数，这时可以把这一列设计成numeric类型。numeric(数据的总列数,小数位数)</p>
<p>numericdouble float</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select sum(price) from shop;  </span><br><span class="line">+------------+</span><br><span class="line">| sum(price) |</span><br><span class="line">+------------+</span><br><span class="line">|      42.77 |</span><br><span class="line">+------------+</span><br></pre></td></tr></table></figure>
<h2 id="5-3、avg函数"><a href="#5-3、avg函数" class="headerlink" title="5.3、avg函数"></a>5.3、avg函数</h2><p>语法：select avg(列名) from 表名;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select avg(price) from shop;   </span><br><span class="line">+------------+</span><br><span class="line">| avg(price) |</span><br><span class="line">+------------+</span><br><span class="line">|   6.110000 |</span><br><span class="line">+------------+</span><br></pre></td></tr></table></figure>
<h2 id="5-4、max函数"><a href="#5-4、max函数" class="headerlink" title="5.4、max函数"></a>5.4、max函数</h2><p>语法：select max(列名) from 表名;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select max(price) from shop;   </span><br><span class="line">+------------+</span><br><span class="line">| max(price) |</span><br><span class="line">+------------+</span><br><span class="line">|      19.95 |</span><br><span class="line">+------------+</span><br></pre></td></tr></table></figure>
<h2 id="5-5、min函数"><a href="#5-5、min函数" class="headerlink" title="5.5、min函数"></a>5.5、min函数</h2><p>语法：select min(列名) from 表名;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select min(price) from shop;   </span><br><span class="line">+------------+</span><br><span class="line">| min(price) |</span><br><span class="line">+------------+</span><br><span class="line">|       1.25 |</span><br><span class="line">+------------+</span><br></pre></td></tr></table></figure>
<h1 id="6、SQL分类"><a href="#6、SQL分类" class="headerlink" title="6、SQL分类"></a>6、SQL分类</h1><h2 id="6-1、DDL-（数据定义问题）"><a href="#6-1、DDL-（数据定义问题）" class="headerlink" title="6.1、DDL （数据定义问题）"></a>6.1、DDL （数据定义问题）</h2><p>数据定义语言 - Data Definition Language</p>
<p>用来定义数据库的对象，如数据表、视图、索引等</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">创建数据库：create database test;</span><br><span class="line">创建视图：create  view  test;</span><br><span class="line">创建索引：create index test;</span><br><span class="line">创建表：create table test1;</span><br></pre></td></tr></table></figure>
<h2 id="6-2、DML-（数据操纵问题）"><a href="#6-2、DML-（数据操纵问题）" class="headerlink" title="6.2、DML  （数据操纵问题）"></a>6.2、DML  （数据操纵问题）</h2><p>数据处理语言 - Data Manipulation Language</p>
<p>在数据库表中更新，增加和删除记录</p>
<p>如 update， insert， delete</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">update tableName set age=&apos;18&apos; where name=&apos;lisi&apos;</span><br><span class="line"></span><br><span class="line">insert into tableName value(&apos;1&apos;,&apos;2&apos;,&apos;3&apos;);</span><br><span class="line"></span><br><span class="line">drop table tableName //删除表操作</span><br></pre></td></tr></table></figure>
<h2 id="6-3、DCL-（数据控制问题）"><a href="#6-3、DCL-（数据控制问题）" class="headerlink" title="6.3、DCL （数据控制问题）"></a>6.3、DCL （数据控制问题）</h2><p>数据控制语言 – Data Control Language</p>
<p>指用于设置用户权限和控制事务语句</p>
<p>如grant，revoke，if…else，while，begintransaction</p>
<h2 id="6-4、DQL-（数据查询问题）"><a href="#6-4、DQL-（数据查询问题）" class="headerlink" title="6.4、DQL （数据查询问题）"></a>6.4、DQL （数据查询问题）</h2><p>数据查询语言 – Data Query Language</p>
<p>select</p>
<h2 id="6-5、小结"><a href="#6-5、小结" class="headerlink" title="6.5、小结"></a>6.5、小结</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">1、创建数据库：create database  test1;</span><br><span class="line">2、使用数据库：use test1;</span><br><span class="line">3、查看当前数据库中的所有表：show tables ;</span><br><span class="line">4、查看所有的数据库：show databases;</span><br><span class="line"></span><br><span class="line">5、删除数据库：drop database test1;</span><br><span class="line">6、删除数据库中的表：drop table t1;</span><br></pre></td></tr></table></figure>
<h1 id="7、数据库的备份与恢复"><a href="#7、数据库的备份与恢复" class="headerlink" title="7、数据库的备份与恢复"></a>7、数据库的备份与恢复</h1><h2 id="7-1、备份命令"><a href="#7-1、备份命令" class="headerlink" title="7.1、备份命令"></a>7.1、备份命令</h2><p>​    在mysql的安装目录的bin目录下有mysqldump命令，可以完成对数据库的备份。</p>
<p>语法：mysqldump -u 用户名 -p 数据库名&gt; 磁盘SQL文件路径</p>
<p>由于mysqldump命令不是sql命令，需要在dos窗口下使用。</p>
<p>​    注意：在备份数据的时候，数据库不会被删除。可以手动删除数据库。同时在恢复数据的时候，不会</p>
<p>自动的给我们创建数据库，仅仅只会恢复数据库中的表和表中的数据。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">mysqldump -uroot -p123456 menagerie &gt;/root/data/menagerie.sql</span><br><span class="line"></span><br><span class="line">//备份的文件</span><br><span class="line">-rw-r--r--. 1 root root 3118 Oct 20 04:04 menagerie.sql</span><br></pre></td></tr></table></figure>
<h2 id="7-2、恢复命令"><a href="#7-2、恢复命令" class="headerlink" title="7.2、恢复命令"></a>7.2、恢复命令</h2><p>恢复数据库，需要手动的先创建数据库：</p>
<p>create database heima2;</p>
<p>语法：mysql -u 用户名-p 导入库名&lt; 硬盘SQL文件绝对路径</p>
<p>需求：</p>
<p>​    1、创建heima8数据库。</p>
<p>​    2、重新开启一个新的dos窗口。</p>
<p>​    3、将mydb2备份的数据表和表数据 恢复到mydb6中。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">//恢复命令</span><br><span class="line">mysql -uroot -p123456 test1&lt;/root/data/menagerie.sql</span><br><span class="line"></span><br><span class="line">//恢复校验</span><br></pre></td></tr></table></figure>
<h1 id="8、多表查询"><a href="#8、多表查询" class="headerlink" title="8、多表查询"></a>8、多表查询</h1><h2 id="8-1、笛卡尔积介绍"><a href="#8-1、笛卡尔积介绍" class="headerlink" title="8.1、笛卡尔积介绍"></a>8.1、笛卡尔积介绍</h2><p>​    笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员</p>
<p>准备数据：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">create table A(</span><br><span class="line">  A_ID int primary key auto_increment,</span><br><span class="line">  A_NAME varchar(20) not null</span><br><span class="line">);</span><br><span class="line">insert into A values(1,&apos;apple&apos;);</span><br><span class="line">insert into A values(2,&apos;orange&apos;);</span><br><span class="line">insert into A values(3,&apos;banana&apos;);</span><br><span class="line"></span><br><span class="line">create table B( </span><br><span class="line">   A_ID int primary key auto_increment,</span><br><span class="line">   B_PRICE double</span><br><span class="line">);</span><br><span class="line">insert into B values(1,2.30);</span><br><span class="line">insert into B values(2,3.50);</span><br><span class="line">insert into B values(4,null);</span><br></pre></td></tr></table></figure>
<p>展示效果：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from A,B;</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">| A_ID | A_NAME | A_ID | B_PRICE |</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">|    1 | apple  |    1 |     2.3 |</span><br><span class="line">|    2 | orange |    1 |     2.3 |</span><br><span class="line">|    3 | banana |    1 |     2.3 |</span><br><span class="line">|    1 | apple  |    2 |     3.5 |</span><br><span class="line">|    2 | orange |    2 |     3.5 |</span><br><span class="line">|    3 | banana |    2 |     3.5 |</span><br><span class="line">|    1 | apple  |    4 |    NULL |</span><br><span class="line">|    2 | orange |    4 |    NULL |</span><br><span class="line">|    3 | banana |    4 |    NULL |</span><br><span class="line">+------+--------+------+---------+</span><br></pre></td></tr></table></figure>
<p>作用：笛卡尔积的数据，对程序是没有意义的，我们需要对笛卡尔积中的数据再次进行过滤。</p>
<p>对于多表查询操作，需要过滤出满足条件的数据，需要把多个表进行连接，连接之后需要加上过滤的条件。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from A,B where B.A_ID=1;</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">| A_ID | A_NAME | A_ID | B_PRICE |</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">|    1 | apple  |    1 |     2.3 |</span><br><span class="line">|    2 | orange |    1 |     2.3 |</span><br><span class="line">|    3 | banana |    1 |     2.3 |</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">3 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from A,B where B.A_ID=1 and A.A_ID=1;</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">| A_ID | A_NAME | A_ID | B_PRICE |</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">|    1 | apple  |    1 |     2.3 |</span><br><span class="line">+------+--------+------+---------+</span><br></pre></td></tr></table></figure>
<h2 id="8-2、内连接"><a href="#8-2、内连接" class="headerlink" title="8.2、内连接"></a>8.2、内连接</h2><p>内连接：<br>语法一：<br>select 列名 , 列名 …. from 表名1,表名2 where 表名1.列名 = 表名2.列名;</p>
<p>语法二：</p>
<p>select * from 表名1  inner join 表名2 on 条件</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from A inner join B on A.A_ID=B.A_ID;</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">| A_ID | A_NAME | A_ID | B_PRICE |</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">|    1 | apple  |    1 |     2.3 |</span><br><span class="line">|    2 | orange |    2 |     3.5 |</span><br><span class="line">+------+--------+------+---------+</span><br></pre></td></tr></table></figure>
<h2 id="8-3、左外连接"><a href="#8-3、左外连接" class="headerlink" title="8.3、左外连接"></a>8.3、左外连接</h2><p>外链接：左外连接、右外连接、全连接、自连接。</p>
<p>左外连接：用左边表去右边表中查询对应记录，不管是否找到，都将显示左边表中全部记录。</p>
<p>即：虽然右表没有香蕉对应的价格，也要把他查询出来。</p>
<p>语法：select * from 表1 left outer join 表2 on 条件;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from A left join B on A.A_ID=B.A_ID;</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">| A_ID | A_NAME | A_ID | B_PRICE |</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">|    1 | apple  |    1 |     2.3 |</span><br><span class="line">|    2 | orange |    2 |     3.5 |</span><br><span class="line">|    3 | banana | NULL |    NULL |</span><br><span class="line">+------+--------+------+---------+</span><br></pre></td></tr></table></figure>
<h2 id="8-4、右外连接"><a href="#8-4、右外连接" class="headerlink" title="8.4、右外连接"></a>8.4、右外连接</h2><p>用右边表去左边表查询对应记录，不管是否找到，右边表全部记录都将显示。</p>
<p>即：不管左方能够找到右方价格对应的水果，都要把左方的价格显示出来。</p>
<p>语法：select * from 表1 right outer join 表2 on 条件;</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from A right join B on A.A_ID=B.A_ID;     </span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">| A_ID | A_NAME | A_ID | B_PRICE |</span><br><span class="line">+------+--------+------+---------+</span><br><span class="line">|    1 | apple  |    1 |     2.3 |</span><br><span class="line">|    2 | orange |    2 |     3.5 |</span><br><span class="line">| NULL | NULL   |    4 |    NULL |</span><br><span class="line">+------+--------+------+---------+</span><br></pre></td></tr></table></figure>
<h2 id="8-5、全外连接"><a href="#8-5、全外连接" class="headerlink" title="8.5、全外连接"></a>8.5、全外连接</h2><p>全外连接：左外连接和右外连接的结果合并，单会去掉重复的记录。<br>select <em> from 表1 full outer join 表2 on 条件<br>select </em> from a full outer join b on a.A_ID = b.A_ID; 但是mysql数据库不支持此语法。</p>
<h2 id="8-6、关联子查询"><a href="#8-6、关联子查询" class="headerlink" title="8.6、关联子查询"></a>8.6、关联子查询</h2><p>子查询：把一个sql的查询结果作为另外一个查询的参数存在。</p>
<h3 id="8-6-1、in和exist关键词的用法"><a href="#8-6-1、in和exist关键词的用法" class="headerlink" title="8.6.1、in和exist关键词的用法"></a>8.6.1、in和exist关键词的用法</h3><p>关联子查询其他的关键字使用：</p>
<p>回忆：age=23 or age=24  等价于 age in (23,24)</p>
<p>in 表示条件应该是在多个列值中。</p>
<p>in：使用在where后面，经常表示是一个列表中的数据，只要被查询的数据在这个列表中存在即可。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from A where A_ID in(1,2,3);</span><br><span class="line">+------+--------+</span><br><span class="line">| A_ID | A_NAME |</span><br><span class="line">+------+--------+</span><br><span class="line">|    1 | apple  |</span><br><span class="line">|    2 | orange |</span><br><span class="line">|    3 | banana |</span><br><span class="line">+------+--------+</span><br><span class="line">3 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from A where A_ID =1 or A_ID =2 or A_ID =3;</span><br><span class="line">+------+--------+</span><br><span class="line">| A_ID | A_NAME |</span><br><span class="line">+------+--------+</span><br><span class="line">|    1 | apple  |</span><br><span class="line">|    2 | orange |</span><br><span class="line">|    3 | banana |</span><br><span class="line">+------+--------+</span><br><span class="line">//not in</span><br><span class="line">mysql&gt; select * from A where A_ID not in (1,2,3,4);</span><br><span class="line">Empty set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from A where A_ID not in (3,4);    </span><br><span class="line">+------+--------+</span><br><span class="line">| A_ID | A_NAME |</span><br><span class="line">+------+--------+</span><br><span class="line">|    1 | apple  |</span><br><span class="line">|    2 | orange |</span><br><span class="line">+------+--------+</span><br><span class="line">2 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>
<p>exists：</p>
<p>exists：表示存在，当子查询的结果存在，就会显示主查询中的所有数据。</p>
<p>使用exists完成：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from A where exists(select A_ID from B);</span><br><span class="line">+------+--------+</span><br><span class="line">| A_ID | A_NAME |</span><br><span class="line">+------+--------+</span><br><span class="line">|    1 | apple  |</span><br><span class="line">|    2 | orange |</span><br><span class="line">|    3 | banana |</span><br><span class="line">+------+--------+</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from A where not exists(select A_ID from B);</span><br><span class="line">Empty set (0.00 sec)</span><br></pre></td></tr></table></figure>
<h3 id="8-6-2、union-和union-all使用法"><a href="#8-6-2、union-和union-all使用法" class="headerlink" title="8.6.2、union 和union  all使用法"></a>8.6.2、union 和union  all使用法</h3><p><strong>UNION 语句</strong>：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）</p>
<p><strong>UNION ALL 语句</strong>：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from A  union   select * from B;   </span><br><span class="line">+------+--------+</span><br><span class="line">| A_ID | A_NAME |</span><br><span class="line">+------+--------+</span><br><span class="line">|    1 | apple  |</span><br><span class="line">|    2 | orange |</span><br><span class="line">|    3 | banana |</span><br><span class="line">|    1 | 2.3    |</span><br><span class="line">|    2 | 3.5    |</span><br><span class="line">|    4 | NULL   |</span><br><span class="line">+------+--------+</span><br><span class="line">6 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from A  union all  select * from B;</span><br><span class="line">+------+--------+</span><br><span class="line">| A_ID | A_NAME |</span><br><span class="line">+------+--------+</span><br><span class="line">|    1 | apple  |</span><br><span class="line">|    2 | orange |</span><br><span class="line">|    3 | banana |</span><br><span class="line">|    1 | 2.3    |</span><br><span class="line">|    2 | 3.5    |</span><br><span class="line">|    4 | NULL   |</span><br><span class="line">+------+--------+</span><br></pre></td></tr></table></figure>
<h3 id="8-6-3、case-when-语句"><a href="#8-6-3、case-when-语句" class="headerlink" title="8.6.3、case when 语句"></a>8.6.3、case when 语句</h3><p>case when 语句语法结构：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">CASE sex  </span><br><span class="line">WHEN &apos;1&apos; THEN &apos;男&apos;  </span><br><span class="line">WHEN &apos;2&apos; THEN &apos;女&apos;  </span><br><span class="line">ELSE &apos;其他&apos; END</span><br></pre></td></tr></table></figure>
<p>准备数据</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">//创建表</span><br><span class="line">create table employee(</span><br><span class="line">empid                   int  ,                                       </span><br><span class="line">deptid                  int   ,                                      </span><br><span class="line">sex                     varchar(20)  ,                                    </span><br><span class="line">salary                  double </span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">//加载数据</span><br><span class="line">1	10	female	5500.0</span><br><span class="line">2	10	male	4500.0</span><br><span class="line">3	20	female	1900.0</span><br><span class="line">4	20	male	4800.0</span><br><span class="line">5	40	female	6500.0</span><br><span class="line">6	40	female	14500.0</span><br><span class="line">7	40	male	44500.0</span><br><span class="line">8	50	male	6500.0</span><br><span class="line">9	50	male	7500.0</span><br><span class="line"></span><br><span class="line">load data local infile &apos;/root/data/emp.txt&apos; into table employee ;</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">select *,</span><br><span class="line">case </span><br><span class="line">when salary &lt; 5000 then &quot;低等收入&quot; </span><br><span class="line">when salary&gt;= 5000 and salary &lt; 10000 then &quot;中等收入&quot;</span><br><span class="line">when salary &gt; 10000 then &quot;高等收入&quot;  </span><br><span class="line">end  as level,</span><br><span class="line">case sex</span><br><span class="line">when &quot;female&quot; then 1 </span><br><span class="line">when &quot;male&quot; then 0</span><br><span class="line">end as flag </span><br><span class="line">from employee;</span><br></pre></td></tr></table></figure>
<h1 id="9、MySQL-数据类型"><a href="#9、MySQL-数据类型" class="headerlink" title="9、MySQL 数据类型"></a>9、MySQL 数据类型</h1><p>MySQL中定义数据字段的类型对你数据库的优化是非常重要的。</p>
<p>MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。</p>
<h2 id="9-1、数值类型"><a href="#9-1、数值类型" class="headerlink" title="9.1、数值类型"></a>9.1、数值类型</h2><p>MySQL支持所有标准SQL数值数据类型。</p>
<p>这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。</p>
<p>关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。</p>
<p>BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。</p>
<p>作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。</p>
<table>
<thead>
<tr>
<th>类型</th>
<th>大小</th>
<th>范围（有符号）</th>
<th>范围（无符号）</th>
<th>用途</th>
</tr>
</thead>
<tbody>
<tr>
<td>TINYINT</td>
<td>1 字节</td>
<td>(-128，127)</td>
<td>(0，255)</td>
<td>小整数值</td>
</tr>
<tr>
<td>SMALLINT</td>
<td>2 字节</td>
<td>(-32 768，32 767)</td>
<td>(0，65 535)</td>
<td>大整数值</td>
</tr>
<tr>
<td>MEDIUMINT</td>
<td>3 字节</td>
<td>(-8 388 608，8 388 607)</td>
<td>(0，16 777 215)</td>
<td>大整数值</td>
</tr>
<tr>
<td>INT或INTEGER</td>
<td>4 字节</td>
<td>(-2 147 483 648，2 147 483 647)</td>
<td>(0，4 294 967 295)</td>
<td>大整数值</td>
</tr>
<tr>
<td>BIGINT</td>
<td>8 字节</td>
<td>(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)</td>
<td>(0，18 446 744 073 709 551 615)</td>
<td>极大整数值</td>
</tr>
<tr>
<td>FLOAT</td>
<td>4 字节</td>
<td>(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)</td>
<td>0，(1.175 494 351 E-38，3.402 823 466 E+38)</td>
<td>单精度浮点数值</td>
</tr>
<tr>
<td>DOUBLE</td>
<td>8 字节</td>
<td>(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td>
<td>0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td>
<td>双精度浮点数值</td>
</tr>
<tr>
<td>DECIMAL</td>
<td>对DECIMAL(M,D) ，如果M&gt;D，为M+2否则为D+2</td>
<td>依赖于M和D的值</td>
<td>依赖于M和D的值</td>
<td>小数值</td>
</tr>
</tbody>
</table>
<hr>
<h2 id="9-2、日期和时间类型"><a href="#9-2、日期和时间类型" class="headerlink" title="9.2、日期和时间类型"></a>9.2、日期和时间类型</h2><p>表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。</p>
<p>每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。</p>
<p>TIMESTAMP类型有专有的自动更新特性，将在后面描述。</p>
<table>
<thead>
<tr>
<th>类型</th>
<th>大小(字节)</th>
<th>范围</th>
<th>格式</th>
<th>用途</th>
</tr>
</thead>
<tbody>
<tr>
<td>DATE</td>
<td>3</td>
<td>1000-01-01/9999-12-31</td>
<td>YYYY-MM-DD</td>
<td>日期值</td>
</tr>
<tr>
<td>TIME</td>
<td>3</td>
<td>‘-838:59:59’/‘838:59:59’</td>
<td>HH:MM:SS</td>
<td>时间值或持续时间</td>
</tr>
<tr>
<td>YEAR</td>
<td>1</td>
<td>1901/2155</td>
<td>YYYY</td>
<td>年份值</td>
</tr>
<tr>
<td>DATETIME</td>
<td>8</td>
<td>1000-01-01 00:00:00/9999-12-31 23:59:59</td>
<td>YYYY-MM-DD HH:MM:SS</td>
<td>混合日期和时间值</td>
</tr>
<tr>
<td>TIMESTAMP</td>
<td>4</td>
<td>1970-01-01 00:00:00/2038结束时间是第 <strong>2147483647</strong> 秒，北京时间 <strong>2038-1-19 11:14:07</strong>，格林尼治时间 2038年1月19日 凌晨 03:14:07</td>
<td>YYYYMMDD HHMMSS</td>
<td>混合日期和时间值，时间戳</td>
</tr>
</tbody>
</table>
<hr>
<h2 id="9-3、字符串类型"><a href="#9-3、字符串类型" class="headerlink" title="9.3、字符串类型"></a>9.3、字符串类型</h2><p>字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。</p>
<table>
<thead>
<tr>
<th>类型</th>
<th>大小</th>
<th>用途</th>
</tr>
</thead>
<tbody>
<tr>
<td>CHAR</td>
<td>0-255字节</td>
<td>定长字符串</td>
</tr>
<tr>
<td>VARCHAR</td>
<td>0-65535 字节</td>
<td>变长字符串</td>
</tr>
<tr>
<td>TINYBLOB</td>
<td>0-255字节</td>
<td>不超过 255 个字符的二进制字符串</td>
</tr>
<tr>
<td>TINYTEXT</td>
<td>0-255字节</td>
<td>短文本字符串</td>
</tr>
<tr>
<td>BLOB</td>
<td>0-65 535字节</td>
<td>二进制形式的长文本数据</td>
</tr>
<tr>
<td>TEXT</td>
<td>0-65 535字节</td>
<td>长文本数据</td>
</tr>
<tr>
<td>MEDIUMBLOB</td>
<td>0-16 777 215字节</td>
<td>二进制形式的中等长度文本数据</td>
</tr>
<tr>
<td>MEDIUMTEXT</td>
<td>0-16 777 215字节</td>
<td>中等长度文本数据</td>
</tr>
<tr>
<td>LONGBLOB</td>
<td>0-4 294 967 295字节</td>
<td>二进制形式的极大文本数据</td>
</tr>
<tr>
<td>LONGTEXT</td>
<td>0-4 294 967 295字节</td>
<td>极大文本数据</td>
</tr>
</tbody>
</table>
<p>CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。</p>
<p>BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。</p>
<p>BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。</p>
<p>有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。</p>
<p>MySQL 5.0 以上的版本：</p>
<p>1、一个汉字占多少长度与编码有关：</p>
<p><strong>UTF－8</strong>：一个汉字＝3个字节</p>
<p><strong>GBK</strong>：一个汉字＝2个字节</p>
<p>2、varchar(n) 表示 n 个字符，无论汉字和英文，Mysql 都能存入 n 个字符，仅是实际字节长度有所区别</p>
<p>3、MySQL 检查长度，可用 SQL 语言来查看：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select LENGTH(fieldname) from tablename</span><br></pre></td></tr></table></figure>
<p><strong>1、整型</strong></p>
<table>
<thead>
<tr>
<th><strong>MySQL数据类型</strong></th>
<th><strong>含义（有符号）</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>tinyint(m)</td>
<td>1个字节  范围(-128~127)</td>
</tr>
<tr>
<td>smallint(m)</td>
<td>2个字节  范围(-32768~32767)</td>
</tr>
<tr>
<td>mediumint(m)</td>
<td>3个字节  范围(-8388608~8388607)</td>
</tr>
<tr>
<td>int(m)</td>
<td>4个字节  范围(-2147483648~2147483647)</td>
</tr>
<tr>
<td>bigint(m)</td>
<td>8个字节  范围(+-9.22*10的18次方)</td>
</tr>
</tbody>
</table>
<p>取值范围如果加了 unsigned，则最大值翻倍，如 tinyint unsigned 的取值范围为(0~256)。</p>
<p>int(m) 里的 m 是表示 SELECT 查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个 m 有什么用。</p>
<p><strong>2、浮点型(float 和 double)</strong></p>
<table>
<thead>
<tr>
<th><strong>MySQL数据类型</strong></th>
<th><strong>含义</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>float(m,d)</td>
<td>单精度浮点型    8位精度(4字节)     m总个数，d小数位</td>
</tr>
<tr>
<td>double(m,d)</td>
<td>双精度浮点型    16位精度(8字节)    m总个数，d小数位</td>
</tr>
</tbody>
</table>
<p>设一个字段定义为 float(5,3)，如果插入一个数 123.45678,实际数据库里存的是 123.457，但总个数还以实际为准，即 6 位。</p>
<p><strong>3、定点数</strong></p>
<p>浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。</p>
<p>decimal(m,d) 参数 m&lt;65 是总个数，d&lt;30 且 d&lt;m 是小数位。</p>
<p><strong>4、字符串(char,varchar,_text)</strong></p>
<table>
<thead>
<tr>
<th><strong>MySQL数据类型</strong></th>
<th><strong>含义</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>char(n)</td>
<td>固定长度，最多255个字符</td>
</tr>
<tr>
<td>varchar(n)</td>
<td>固定长度，最多65535个字符</td>
</tr>
<tr>
<td>tinytext</td>
<td>可变长度，最多255个字符</td>
</tr>
<tr>
<td>text</td>
<td>可变长度，最多65535个字符</td>
</tr>
<tr>
<td>mediumtext</td>
<td>可变长度，最多2的24次方-1个字符</td>
</tr>
<tr>
<td>longtext</td>
<td>可变长度，最多2的32次方-1个字符</td>
</tr>
</tbody>
</table>
<p>char 和 varchar：</p>
<ul>
<li>** 1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格，varchar 不限于此。</li>
<li>** 2.char(n) 固定长度，char(4) 不管是存入几个字符，都将占用 4 个字节，varchar 是存入的实际字符数 +1 个字节（n&lt;=255）或2个字节(n&gt;255)，所以 varchar(4),存入 3 个字符将占用 4 个字节。</li>
<li>** 3.char 类型的字符串检索速度要比 varchar 类型的快。</li>
</ul>
<p>varchar 和 text：</p>
<ul>
<li>** 1.varchar 可指定 n，text 不能指定，内部存储 varchar 是存入的实际字符数 +1 个字节（n&lt;=255）或 2 个字节(n&gt;255)，text 是实际字符数 +2 个字节。</li>
<li>** 2.text 类型不能有默认值。</li>
<li>** 3.varchar 可直接创建索引，text 创建索引要指定前多少个字符。varchar 查询速度快于 text, 在都创建索引的情况下，text 的索引似乎不起作用。</li>
</ul>
<p><strong>5.二进制数据(_Blob)</strong></p>
<ul>
<li>** 1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。</li>
<li>** 2._BLOB存储的数据只能整体读出。</li>
<li>** 3._TEXT可以指定字符集，_BLOB不用指定字符集。</li>
</ul>
<p><strong>6.日期时间类型</strong></p>
<table>
<thead>
<tr>
<th><strong>MySQL数据类型</strong></th>
<th><strong>含义</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>date</td>
<td>日期 ‘2008-12-2’</td>
</tr>
<tr>
<td>time</td>
<td>时间 ‘12:25:36’</td>
</tr>
<tr>
<td>datetime</td>
<td>日期时间 ‘2008-12-2 22:06:44’</td>
</tr>
<tr>
<td>timestamp</td>
<td>自动存储记录修改时间</td>
</tr>
</tbody>
</table>
<p>若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。</p>
<p><strong>数据类型的属性</strong></p>
<table>
<thead>
<tr>
<th><strong>MySQL关键字</strong></th>
<th><strong>含义</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>NULL</td>
<td>数据列可包含NULL值</td>
</tr>
<tr>
<td>NOT NULL</td>
<td>数据列不允许包含NULL值</td>
</tr>
<tr>
<td>DEFAULT</td>
<td>默认值</td>
</tr>
<tr>
<td>PRIMARY KEY</td>
<td>主键</td>
</tr>
<tr>
<td>AUTO_INCREMENT</td>
<td>自动递增，适用于整数类型</td>
</tr>
<tr>
<td>UNSIGNED</td>
<td>无符号</td>
</tr>
<tr>
<td>CHARACTER SET name</td>
<td>指定一个字符集</td>
</tr>
</tbody>
</table>
<h1 id="10、MySQL-GROUP-BY-语句"><a href="#10、MySQL-GROUP-BY-语句" class="headerlink" title="10、MySQL GROUP BY 语句"></a>10、MySQL GROUP BY 语句</h1><p>GROUP BY 语句根据一个或多个列对结果集进行分组。</p>
<p>在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。</p>
<p>语法结构：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">SELECT column_name, function(column_name)</span><br><span class="line">FROM table_name</span><br><span class="line">WHERE column_name operator value</span><br><span class="line">GROUP BY column_name;</span><br></pre></td></tr></table></figure>
<p>准备数据：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE `employee_tbl` (</span><br><span class="line">  `id` int(11) NOT NULL,</span><br><span class="line">  `name` char(10) NOT NULL DEFAULT &apos;&apos;,</span><br><span class="line">  `date` datetime NOT NULL,</span><br><span class="line">  `singin` tinyint(4) NOT NULL DEFAULT &apos;0&apos; COMMENT &apos;登录次数&apos;,</span><br><span class="line">  PRIMARY KEY (`id`)</span><br><span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8;</span><br><span class="line"></span><br><span class="line">-- ----------------------------</span><br><span class="line"></span><br><span class="line">INSERT INTO `employee_tbl` VALUES (&apos;1&apos;, &apos;小明&apos;, &apos;2016-04-22 15:25:33&apos;, &apos;1&apos;), (&apos;2&apos;, &apos;小王&apos;, &apos;2016-04-20 15:25:47&apos;, &apos;3&apos;), (&apos;3&apos;, &apos;小丽&apos;, &apos;2016-04-19 15:26:02&apos;, &apos;2&apos;), (&apos;4&apos;, &apos;小王&apos;, &apos;2016-04-07 15:26:14&apos;, &apos;4&apos;), (&apos;5&apos;, &apos;小明&apos;, &apos;2016-04-11 15:26:40&apos;, &apos;4&apos;), (&apos;6&apos;, &apos;小明&apos;, &apos;2016-04-04 15:26:54&apos;, &apos;2&apos;);</span><br></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;</span><br><span class="line">+--------+----------+</span><br><span class="line">| name   | COUNT(*) |</span><br><span class="line">+--------+----------+</span><br><span class="line">| 小丽 |        1 |</span><br><span class="line">| 小明 |        3 |</span><br><span class="line">| 小王 |        2 |</span><br><span class="line">+--------+----------+</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from employee_tbl;</span><br><span class="line">+----+------+---------------------+--------+</span><br><span class="line">| id | name | date                | singin |</span><br><span class="line">+----+------+---------------------+--------+</span><br><span class="line">|  1 | ??   | 2016-04-22 15:25:33 |      1 |</span><br><span class="line">|  2 | ??   | 2016-04-20 15:25:47 |      3 |</span><br><span class="line">|  3 | ??   | 2016-04-19 15:26:02 |      2 |</span><br><span class="line">|  4 | ??   | 2016-04-07 15:26:14 |      4 |</span><br><span class="line">|  5 | ??   | 2016-04-11 15:26:40 |      4 |</span><br><span class="line">|  6 | ??   | 2016-04-04 15:26:54 |      2 |</span><br><span class="line">+----+------+---------------------+--------+</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from employee_tbl group by singin;</span><br><span class="line">+----+------+---------------------+--------+</span><br><span class="line">| id | name | date                | singin |</span><br><span class="line">+----+------+---------------------+--------+</span><br><span class="line">|  1 | ??   | 2016-04-22 15:25:33 |      1 |</span><br><span class="line">|  3 | ??   | 2016-04-19 15:26:02 |      2 |</span><br><span class="line">|  2 | ??   | 2016-04-20 15:25:47 |      3 |</span><br><span class="line">|  4 | ??   | 2016-04-07 15:26:14 |      4 |</span><br><span class="line">+----+------+---------------------+--------+</span><br></pre></td></tr></table></figure>
<p>注意：</p>
<p>1、group by 可以实现一个最简单的去重查询，假设想看下有哪些员工，除了用 distinct,还可以用：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT name FROM employee_tbl GROUP BY name;</span><br></pre></td></tr></table></figure>
<p>返回的结果集就是所有员工的名字。</p>
<p>2、分组后的条件使用 HAVING 来限定，WHERE 是对原始数据进行条件限制。几个关键字的使用顺序为 where 、group by 、having、order by ，例如：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT name ,sum(*)  FROM employee_tbl WHERE id&lt;&gt;1 GROUP BY name  HAVING sum(*)&gt;5 ORDER BY sum(*) DESC;</span><br></pre></td></tr></table></figure>
<h1 id="11、MySQL-LIKE-子句"><a href="#11、MySQL-LIKE-子句" class="headerlink" title="11、MySQL LIKE 子句"></a>11、MySQL LIKE 子句</h1><p>我们知道在 MySQL 中使用 SQL SELECT 命令来读取数据， 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。</p>
<p>WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 “company = ‘test1”。</p>
<p>但是有时候我们需要获取 company 字段含有 “it” 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。</p>
<p>SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。</p>
<p>如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。</p>
<p>语法：</p>
<p>以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT field1, field2,...fieldN </span><br><span class="line">FROM table_name</span><br><span class="line">WHERE field1 LIKE condition1 [AND [OR]] filed2 = &apos;somevalue&apos;</span><br></pre></td></tr></table></figure>
<ul>
<li>你可以在 WHERE 子句中指定任何条件。</li>
<li>你可以在 WHERE 子句中使用LIKE子句。</li>
<li>你可以使用LIKE子句代替等号 =。</li>
<li>LIKE 通常与 % 一同使用，类似于一个元字符的搜索。</li>
<li>你可以使用 AND 或者 OR 指定一个或多个条件。</li>
<li>你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from pet where species like &apos;%d%&apos;;</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br><span class="line">| name     | owner  | species | sex  | birth      | death      |</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br><span class="line">| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |</span><br><span class="line">| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |</span><br><span class="line">| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |</span><br><span class="line">| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |</span><br><span class="line">| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |</span><br><span class="line">+----------+--------+---------+------+------------+------------+</span><br></pre></td></tr></table></figure>
<h1 id="12、MySQL-NULL-值处理"><a href="#12、MySQL-NULL-值处理" class="headerlink" title="12、MySQL NULL 值处理"></a>12、MySQL NULL 值处理</h1><p>我们已经知道 MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。</p>
<p>为了处理这种情况，MySQL提供了三大运算符:</p>
<ul>
<li><strong>IS NULL:</strong> 当列的值是 NULL,此运算符返回 true。</li>
<li><strong>IS NOT NULL:</strong> 当列的值不为 NULL, 运算符返回 true。</li>
<li><strong>&lt;=&gt;:</strong> 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。</li>
</ul>
<p>关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。</p>
<p>在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。</p>
<p>MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。</p>
<h1 id="13、MySQL-元数据"><a href="#13、MySQL-元数据" class="headerlink" title="13、MySQL 元数据"></a>13、MySQL 元数据</h1><p>你可能想知道MySQL以下三种信息：</p>
<ul>
<li><strong>查询结果信息：</strong> SELECT, UPDATE 或 DELETE语句影响的记录数。</li>
<li><strong>数据库和数据表的信息：</strong> 包含了数据库及数据表的结构信息。</li>
<li><strong>MySQL服务器信息：</strong> 包含了数据库服务器的当前状态，版本号等。</li>
</ul>
<p>在MySQL的命令提示符中，我们可以很容易的获取以上服务器信息。</p>
<h2 id="获取服务器元数据"><a href="#获取服务器元数据" class="headerlink" title="获取服务器元数据"></a>获取服务器元数据</h2><p>以下命令语句可以在 MySQL 的命令提示符使用，也可以在脚本中 使用，如PHP脚本。</p>
<table>
<thead>
<tr>
<th>命令</th>
<th>描述</th>
</tr>
</thead>
<tbody>
<tr>
<td>SELECT VERSION( )</td>
<td>服务器版本信息</td>
</tr>
<tr>
<td>SELECT DATABASE( )</td>
<td>当前数据库名 (或者返回空)</td>
</tr>
<tr>
<td>SELECT USER( )</td>
<td>当前用户名</td>
</tr>
<tr>
<td>SHOW STATUS</td>
<td>服务器状态</td>
</tr>
<tr>
<td>SHOW VARIABLES</td>
<td>服务器配置变量</td>
</tr>
</tbody>
</table>
<h1 id="14、MySQL-ALTER命令"><a href="#14、MySQL-ALTER命令" class="headerlink" title="14、MySQL ALTER命令"></a>14、MySQL ALTER命令</h1><p>当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。</p>
<h2 id="14-1、删除、添加或修改表字段"><a href="#14-1、删除、添加或修改表字段" class="headerlink" title="14.1、删除、添加或修改表字段"></a>14.1、删除、添加或修改表字段</h2><p>如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; ALTER TABLE testalter_tbl  DROP i;</span><br></pre></td></tr></table></figure>
<p>如果数据表中只剩余一个字段则无法使用DROP来删除字段。</p>
<p>MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; ALTER TABLE testalter_tbl ADD i INT;</span><br></pre></td></tr></table></figure>
<p>执行以上命令后，i 字段会自动添加到数据表字段的末尾</p>
<h2 id="14-2、修改字段类型及名称"><a href="#14-2、修改字段类型及名称" class="headerlink" title="14.2、修改字段类型及名称"></a>14.2、修改字段类型及名称</h2><p>如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。</p>
<p>例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; ALTER TABLE testalter_tbl MODIFY c CHAR(10);</span><br></pre></td></tr></table></figure>
<h2 id="14-3、修改表名"><a href="#14-3、修改表名" class="headerlink" title="14.3、修改表名"></a>14.3、修改表名</h2><p>如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。</p>
<p>尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; ALTER TABLE testalter_tbl RENAME TO alter_tbl;</span><br></pre></td></tr></table></figure>
<h1 id="15、MySQL-函数"><a href="#15、MySQL-函数" class="headerlink" title="15、MySQL 函数"></a>15、MySQL 函数</h1><p>MySQL 有很多内置的函数，以下列出了这些函数的说明。</p>
<h2 id="15-1、MySQL-字符串函数"><a href="#15-1、MySQL-字符串函数" class="headerlink" title="15.1、MySQL 字符串函数"></a>15.1、MySQL 字符串函数</h2><table>
<thead>
<tr>
<th>函数</th>
<th>描述</th>
<th>实例</th>
</tr>
</thead>
<tbody>
<tr>
<td>ASCII(s)</td>
<td>返回字符串 s 的第一个字符的 ASCII 码。</td>
<td>返回 CustomerName 字段第一个字母的 ASCII 码：<code>SELECT ASCII(CustomerName) AS NumCodeOfFirstCharFROM Customers;</code></td>
</tr>
<tr>
<td>CHAR_LENGTH(s)</td>
<td>返回字符串 s 的字符数</td>
<td>返回字符串 test1 的字符数<code>SELECT CHAR_LENGTH(&quot;test1&quot;) AS LengthOfString;</code></td>
</tr>
<tr>
<td>CHARACTER_LENGTH(s)</td>
<td>返回字符串 s 的字符数</td>
<td>返回字符串 test1 的字符数<code>SELECT CHARACTER_LENGTH(&quot;test1&quot;) AS LengthOfString;</code></td>
</tr>
<tr>
<td>CONCAT(s1,s2…sn)</td>
<td>字符串 s1,s2 等多个字符串合并为一个字符串</td>
<td>合并多个字符串<code>SELECT CONCAT(&quot;SQL &quot;, &quot;test1 &quot;, &quot;Gooogle &quot;, &quot;Facebook&quot;) AS ConcatenatedString;</code></td>
</tr>
<tr>
<td>CONCAT_WS(x, s1,s2…sn)</td>
<td>同 CONCAT(s1,s2,…) 函数，但是每个字符串直接要加上 x，x 可以是分隔符</td>
<td>合并多个字符串，并添加分隔符：<code>SELECT CONCAT_WS(&quot;-&quot;, &quot;SQL&quot;, &quot;Tutorial&quot;, &quot;is&quot;, &quot;fun!&quot;)AS ConcatenatedString;</code></td>
</tr>
<tr>
<td>FIELD(s,s1,s2…)</td>
<td>返回第一个字符串 s 在字符串列表(s1,s2…)中的位置</td>
<td>返回字符串 c 在列表值中的位置：<code>SELECT FIELD(&quot;c&quot;, &quot;a&quot;, &quot;b&quot;, &quot;c&quot;, &quot;d&quot;, &quot;e&quot;);</code></td>
</tr>
<tr>
<td>FIND_IN_SET(s1,s2)</td>
<td>返回在字符串s2中与s1匹配的字符串的位置</td>
<td>返回字符串 c 在指定字符串中的位置：<code>SELECT FIND_IN_SET(&quot;c&quot;, &quot;a,b,c,d,e&quot;);</code></td>
</tr>
<tr>
<td>FORMAT(x,n)</td>
<td>函数可以将数字 x 进行格式化 “#,### .##”, 将 x 保留到小数点后 n 位，最后一位四舍五入。</td>
<td>格式化数字 “#,### .##” 形式：<code>SELECT FORMAT(250500.5634, 2);     -- 输出 250,500.56</code></td>
</tr>
<tr>
<td>INSERT(s1,x,len,s2)</td>
<td>字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串</td>
<td>从字符串第一个位置开始的 6 个字符替换为 test1：<code>SELECT INSERT(&quot;google.com&quot;, 1, 6, &quot;runnob&quot;);  -- 输出：test1.com</code></td>
</tr>
<tr>
<td>LOCATE(s1,s)</td>
<td>从字符串 s 中获取 s1 的开始位置</td>
<td>获取 b 在字符串 abc 中的位置：<code>SELECT INSTR(&#39;abc&#39;,&#39;b&#39;) -- 2</code></td>
</tr>
<tr>
<td>LCASE(s)</td>
<td>将字符串 s 的所有字母变成小写字母</td>
<td>字符串 test1 转换为小写：<code>SELECT LOWER(&#39;test1&#39;) -- test1</code></td>
</tr>
<tr>
<td>LEFT(s,n)</td>
<td>返回字符串 s 的前 n 个字符</td>
<td>返回字符串 test1 中的前两个字符：<code>SELECT LEFT(&#39;test1&#39;,2) -- it</code></td>
</tr>
<tr>
<td>LEFT(s,n)</td>
<td>返回字符串 s 的前 n 个字符</td>
<td>返回字符串 abcde 的前两个字符：<code>SELECT LEFT(&#39;abcde&#39;,2) -- ab</code></td>
</tr>
<tr>
<td>LOCATE(s1,s)</td>
<td>从字符串 s 中获取 s1 的开始位置</td>
<td>返回字符串 abc 中 b 的位置：<code>SELECT LOCATE(&#39;b&#39;, &#39;abc&#39;) -- 2</code></td>
</tr>
<tr>
<td>LOWER(s)</td>
<td>将字符串 s 的所有字母变成小写字母</td>
<td>字符串 test1 转换为小写：<code>SELECT LOWER(&#39;test1&#39;) -- test1</code></td>
</tr>
<tr>
<td>LPAD(s1,len,s2)</td>
<td>在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len</td>
<td>将字符串 xx 填充到 abc 字符串的开始处：<code>SELECT LPAD(&#39;abc&#39;,5,&#39;xx&#39;) -- xxabc</code></td>
</tr>
<tr>
<td>LTRIM(s)</td>
<td>去掉字符串 s 开始处的空格</td>
<td>去掉字符串 test1开始处的空格：<code>SELECT LTRIM(&quot;    test1&quot;) AS LeftTrimmedString;-- test1</code></td>
</tr>
<tr>
<td>MID(s,n,len)</td>
<td>从字符串 s 的 start 位置截取长度为 length 的子字符串，同 SUBSTRING(s,n,len)</td>
<td>从字符串 test1 中的第 2 个位置截取 3个 字符：<code>SELECT MID(&quot;test1&quot;, 2, 3) AS ExtractString; -- UNO</code></td>
</tr>
<tr>
<td>POSITION(s1 IN s)</td>
<td>从字符串 s 中获取 s1 的开始位置</td>
<td>返回字符串 abc 中 b 的位置：<code>SELECT POSITION(&#39;b&#39; in &#39;abc&#39;) -- 2</code></td>
</tr>
<tr>
<td>REPEAT(s,n)</td>
<td>将字符串 s 重复 n 次</td>
<td>将字符串 test1 重复三次：<code>SELECT REPEAT(&#39;test1&#39;,3) -- test1test1test1</code></td>
</tr>
<tr>
<td>REPLACE(s,s1,s2)</td>
<td>将字符串 s2 替代字符串 s 中的字符串 s1</td>
<td>将字符串 abc 中的字符 a 替换为字符 x：<code>SELECT REPLACE(&#39;abc&#39;,&#39;a&#39;,&#39;x&#39;) --xbc</code></td>
</tr>
<tr>
<td>REVERSE(s)</td>
<td>将字符串s的顺序反过来</td>
<td>将字符串 abc 的顺序反过来：<code>SELECT REVERSE(&#39;abc&#39;) -- cba</code></td>
</tr>
<tr>
<td>RIGHT(s,n)</td>
<td>返回字符串 s 的后 n 个字符</td>
<td>返回字符串 test1 的后两个字符：<code>SELECT RIGHT(&#39;test1&#39;,2) -- ob</code></td>
</tr>
<tr>
<td>RPAD(s1,len,s2)</td>
<td>在字符串 s1 的结尾处添加字符串 s1，使字符串的长度达到 len</td>
<td>将字符串 xx 填充到 abc 字符串的结尾处：<code>SELECT RPAD(&#39;abc&#39;,5,&#39;xx&#39;) -- abcxx</code></td>
</tr>
<tr>
<td>RTRIM(s)</td>
<td>去掉字符串 s 结尾处的空格</td>
<td>去掉字符串 test1 的末尾空格：<code>SELECT RTRIM(&quot;test1     &quot;) AS RightTrimmedString;   -- test1</code></td>
</tr>
<tr>
<td>SPACE(n)</td>
<td>返回 n 个空格</td>
<td>返回 10 个空格：<code>SELECT SPACE(10);</code></td>
</tr>
<tr>
<td>STRCMP(s1,s2)</td>
<td>比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1&gt;s2 返回 1，如果 s1&lt;s2 返回 -1</td>
<td>比较字符串：<code>SELECT STRCMP(&quot;test1&quot;, &quot;test1&quot;);  -- 0</code></td>
</tr>
<tr>
<td>SUBSTR(s, start, length)</td>
<td>从字符串 s 的 start 位置截取长度为 length 的子字符串</td>
<td>从字符串 test1 中的第 2 个位置截取 3个 字符：<code>SELECT SUBSTR(&quot;test1&quot;, 2, 3) AS ExtractString; -- UNO</code></td>
</tr>
<tr>
<td>SUBSTRING(s, start, length)</td>
<td>从字符串 s 的 start 位置截取长度为 length 的子字符串</td>
<td>从字符串 test1 中的第 2 个位置截取 3个 字符：<code>SELECT SUBSTRING(&quot;test1&quot;, 2, 3) AS ExtractString; -- UNO</code></td>
</tr>
<tr>
<td>SUBSTRING_INDEX(s, delimiter, number)</td>
<td>返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数，返回第 number 个字符左边的字符串。如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。</td>
<td><code>SELECT SUBSTRING_INDEX(&#39;a*b&#39;,&#39;*&#39;,1) -- aSELECT SUBSTRING_INDEX(&#39;a*b&#39;,&#39;*&#39;,-1)    -- bSELECT SUBSTRING_INDEX(SUBSTRING_INDEX(&#39;a*b*c*d*e&#39;,&#39;*&#39;,3),&#39;*&#39;,-1)    -- c</code></td>
</tr>
<tr>
<td>TRIM(s)</td>
<td>去掉字符串 s 开始和结尾处的空格</td>
<td>去掉字符串 test1 的首尾空格：<code>SELECT TRIM(&#39;    test1    &#39;) AS TrimmedString;</code></td>
</tr>
<tr>
<td>UCASE(s)</td>
<td>将字符串转换为大写</td>
<td>将字符串 test1 转换为大写：<code>SELECT UCASE(&quot;test1&quot;); -- test1</code></td>
</tr>
<tr>
<td>UPPER(s)</td>
<td>将字符串转换为大写</td>
<td>将字符串 test1 转换为大写：<code>SELECT UPPER(&quot;test1&quot;); -- test1</code></td>
</tr>
</tbody>
</table>
<hr>
<h2 id="15-2、MySQL-数字函数"><a href="#15-2、MySQL-数字函数" class="headerlink" title="15.2、MySQL 数字函数"></a>15.2、MySQL 数字函数</h2><table>
<thead>
<tr>
<th>函数名</th>
<th>描述</th>
<th>实例</th>
</tr>
</thead>
<tbody>
<tr>
<td>ABS(x)</td>
<td>返回 x 的绝对值</td>
<td>返回 -1 的绝对值：<code>SELECT ABS(-1) -- 返回1</code></td>
</tr>
<tr>
<td>ACOS(x)</td>
<td>求 x 的反余弦值(参数是弧度)</td>
<td><code>SELECT ACOS(0.25);</code></td>
</tr>
<tr>
<td>ASIN(x)</td>
<td>求反正弦值(参数是弧度)</td>
<td><code>SELECT ASIN(0.25);</code></td>
</tr>
<tr>
<td>ATAN(x)</td>
<td>求反正切值(参数是弧度)</td>
<td><code>SELECT ATAN(2.5);</code></td>
</tr>
<tr>
<td>ATAN2(n, m)</td>
<td>求反正切值(参数是弧度)</td>
<td><code>SELECT ATAN2(-0.8, 2);</code></td>
</tr>
<tr>
<td>AVG(expression)</td>
<td>返回一个表达式的平均值，expression 是一个字段</td>
<td>返回 Products 表中Price 字段的平均值：<code>SELECT AVG(Price) AS AveragePrice FROM Products;</code></td>
</tr>
<tr>
<td>CEIL(x)</td>
<td>返回大于或等于 x 的最小整数</td>
<td><code>SELECT CEIL(1.5) -- 返回2</code></td>
</tr>
<tr>
<td>CEILING(x)</td>
<td>返回大于或等于 x 的最小整数</td>
<td><code>SELECT CEIL(1.5) -- 返回2</code></td>
</tr>
<tr>
<td>COS(x)</td>
<td>求余弦值(参数是弧度)</td>
<td><code>SELECT COS(2);</code></td>
</tr>
<tr>
<td>COT(x)</td>
<td>求余切值(参数是弧度)</td>
<td><code>SELECT COT(6);</code></td>
</tr>
<tr>
<td>COUNT(expression)</td>
<td>返回查询的记录总数，expression 参数是一个字段或者 * 号</td>
<td>返回 Products 表中 products 字段总共有多少条记录：<code>SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;</code></td>
</tr>
<tr>
<td>DEGREES(x)</td>
<td>将弧度转换为角度</td>
<td><code>SELECT DEGREES(3.1415926535898) -- 180</code></td>
</tr>
<tr>
<td>n DIV m</td>
<td>整除，n 为被除数，m 为除数</td>
<td>计算 10 除于 5：<code>SELECT 10 DIV 5;  -- 2</code></td>
</tr>
<tr>
<td>EXP(x)</td>
<td>返回 e 的 x 次方</td>
<td>计算 e 的三次方：<code>SELECT EXP(3) -- 20.085536923188</code></td>
</tr>
<tr>
<td>FLOOR(x)</td>
<td>返回小于或等于 x 的最大整数</td>
<td>小于或等于 1.5 的整数：<code>SELECT FLOOR(1.5) -- 返回1</code></td>
</tr>
<tr>
<td>GREATEST(expr1, expr2, expr3, …)</td>
<td>返回列表中的最大值</td>
<td>返回以下数字列表中的最大值：<code>SELECT GREATEST(3, 12, 34, 8, 25); -- 34</code>返回以下字符串列表中的最大值：<code>SELECT GREATEST(&quot;Google&quot;, &quot;test1&quot;, &quot;Apple&quot;);   -- test1</code></td>
</tr>
<tr>
<td>LEAST(expr1, expr2, expr3, …)</td>
<td>返回列表中的最小值</td>
<td>返回以下数字列表中的最小值：<code>SELECT LEAST(3, 12, 34, 8, 25); -- 3</code>返回以下字符串列表中的最小值：<code>SELECT LEAST(&quot;Google&quot;, &quot;test1&quot;, &quot;Apple&quot;);   -- Apple</code></td>
</tr>
<tr>
<td><a href="http://www.test1.com/mysql/func_mysql_ln.asp" target="_blank" rel="noopener">LN</a></td>
<td>返回数字的自然对数</td>
<td>返回 2 的自然对数：<code>SELECT LN(2);  -- 0.6931471805599453</code></td>
</tr>
<tr>
<td>LOG(x)</td>
<td>返回自然对数(以 e 为底的对数)</td>
<td><code>SELECT LOG(20.085536923188) -- 3</code></td>
</tr>
<tr>
<td>LOG10(x)</td>
<td>返回以 10 为底的对数</td>
<td><code>SELECT LOG10(100) -- 2</code></td>
</tr>
<tr>
<td>LOG2(x)</td>
<td>返回以 2 为底的对数</td>
<td>返回以 2 为底 6 的对数：<code>SELECT LOG2(6);  -- 2.584962500721156</code></td>
</tr>
<tr>
<td>MAX(expression)</td>
<td>返回字段 expression 中的最大值</td>
<td>返回数据表 Products 中字段 Price 的最大值：<code>SELECT MAX(Price) AS LargestPrice FROM Products;</code></td>
</tr>
<tr>
<td>MIN(expression)</td>
<td>返回字段 expression 中的最小值</td>
<td>返回数据表 Products 中字段 Price 的最小值：<code>SELECT MIN(Price) AS LargestPrice FROM Products;</code></td>
</tr>
<tr>
<td>MOD(x,y)</td>
<td>返回 x 除以 y 以后的余数</td>
<td>5 除于 2 的余数：<code>SELECT MOD(5,2) -- 1</code></td>
</tr>
<tr>
<td>PI()</td>
<td>返回圆周率(3.141593）</td>
<td><code>SELECT PI() --3.141593</code></td>
</tr>
<tr>
<td>POW(x,y)</td>
<td>返回 x 的 y 次方</td>
<td>2 的 3 次方：<code>SELECT POW(2,3) -- 8</code></td>
</tr>
<tr>
<td>POWER(x,y)</td>
<td>返回 x 的 y 次方</td>
<td>2 的 3 次方：<code>SELECT POWER(2,3) -- 8</code></td>
</tr>
<tr>
<td>RADIANS(x)</td>
<td>将角度转换为弧度</td>
<td>180 度转换为弧度：<code>SELECT RADIANS(180) -- 3.1415926535898</code></td>
</tr>
<tr>
<td>RAND()</td>
<td>返回 0 到 1 的随机数</td>
<td><code>SELECT RAND() --0.93099315644334</code></td>
</tr>
<tr>
<td>ROUND(x)</td>
<td>返回离 x 最近的整数</td>
<td><code>SELECT ROUND(1.23456) --1</code></td>
</tr>
<tr>
<td>SIGN(x)</td>
<td>返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1</td>
<td><code>SELECT SIGN(-10) -- (-1)</code></td>
</tr>
<tr>
<td>SIN(x)</td>
<td>求正弦值(参数是弧度)</td>
<td><code>SELECT SIN(RADIANS(30)) -- 0.5</code></td>
</tr>
<tr>
<td>SQRT(x)</td>
<td>返回x的平方根</td>
<td>25 的平方根：<code>SELECT SQRT(25) -- 5</code></td>
</tr>
<tr>
<td>SUM(expression)</td>
<td>返回指定字段的总和</td>
<td>计算 OrderDetails 表中字段 Quantity 的总和：<code>SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;</code></td>
</tr>
<tr>
<td>TAN(x)</td>
<td>求正切值(参数是弧度)</td>
<td><code>SELECT TAN(1.75);  -- -5.52037992250933</code></td>
</tr>
<tr>
<td>TRUNCATE(x,y)</td>
<td>返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）</td>
<td><code>SELECT TRUNCATE(1.23456,3) -- 1.234</code></td>
</tr>
</tbody>
</table>
<hr>
<h2 id="15-3、MySQL-日期函数"><a href="#15-3、MySQL-日期函数" class="headerlink" title="15.3、MySQL 日期函数"></a>15.3、MySQL 日期函数</h2><table>
<thead>
<tr>
<th>函数名</th>
<th>描述</th>
<th>实例</th>
</tr>
</thead>
<tbody>
<tr>
<td>ADDDATE(d,n)</td>
<td>计算其实日期 d 加上 n 天的日期</td>
<td><code>SELECT ADDDATE(&quot;2017-06-15&quot;, INTERVAL 10 DAY);-&gt;2017-06-25</code></td>
</tr>
<tr>
<td>ADDTIME(t,n)</td>
<td>时间 t 加上 n 秒的时间</td>
<td><code>SELECT ADDTIME(&#39;2011-11-11 11:11:11&#39;, 5)-&gt;2011-11-11 11:11:16 (秒)</code></td>
</tr>
<tr>
<td>CURDATE()</td>
<td>返回当前日期</td>
<td><code>SELECT CURDATE();-&gt; 2018-09-19</code></td>
</tr>
<tr>
<td>CURRENT_DATE()</td>
<td>返回当前日期</td>
<td><code>SELECT CURRENT_DATE();-&gt; 2018-09-19</code></td>
</tr>
<tr>
<td>CURRENT_TIME</td>
<td>返回当前时间</td>
<td><code>SELECT CURRENT_TIME();-&gt; 19:59:02</code></td>
</tr>
<tr>
<td>CURRENT_TIMESTAMP()</td>
<td>返回当前日期和时间</td>
<td><code>SELECT CURRENT_TIMESTAMP()-&gt; 2018-09-19 20:57:43</code></td>
</tr>
<tr>
<td>CURTIME()</td>
<td>返回当前时间</td>
<td><code>SELECT CURTIME();-&gt; 19:59:02</code></td>
</tr>
<tr>
<td>DATE()</td>
<td>从日期或日期时间表达式中提取日期值</td>
<td><code>SELECT DATE(&quot;2017-06-15&quot;);    -&gt; 2017-06-15</code></td>
</tr>
<tr>
<td>DATEDIFF(d1,d2)</td>
<td>计算日期 d1-&gt;d2 之间相隔的天数</td>
<td><code>SELECT DATEDIFF(&#39;2001-01-01&#39;,&#39;2001-02-02&#39;)-&gt; -32</code></td>
</tr>
<tr>
<td>DATE_ADD(d，INTERVAL expr type)</td>
<td>计算起始日期 d 加上一个时间段后的日期</td>
<td><code>SELECT ADDDATE(&#39;2011-11-11 11:11:11&#39;,1)-&gt; 2011-11-12 11:11:11    (默认是天)SELECT ADDDATE(&#39;2011-11-11 11:11:11&#39;, INTERVAL 5 MINUTE)-&gt; 2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似)</code></td>
</tr>
<tr>
<td>DATE_FORMAT(d,f)</td>
<td>按表达式 f的要求显示日期 d</td>
<td><code>SELECT DATE_FORMAT(&#39;2011-11-11 11:11:11&#39;,&#39;%Y-%m-%d %r&#39;)-&gt; 2011-11-11 11:11:11 AM</code></td>
</tr>
<tr>
<td>DATE_SUB(date,INTERVAL expr type)</td>
<td>函数从日期减去指定的时间间隔。</td>
<td>Orders 表中 OrderDate 字段减去 2 天：<code>SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDateFROM Orders</code></td>
</tr>
<tr>
<td>DAY(d)</td>
<td>返回日期值 d 的日期部分</td>
<td><code>SELECT DAY(&quot;2017-06-15&quot;);  -&gt; 15</code></td>
</tr>
<tr>
<td>DAYNAME(d)</td>
<td>返回日期 d 是星期几，如 Monday,Tuesday</td>
<td><code>SELECT DAYNAME(&#39;2011-11-11 11:11:11&#39;)-&gt;Friday</code></td>
</tr>
<tr>
<td>DAYOFMONTH(d)</td>
<td>计算日期 d 是本月的第几天</td>
<td><code>SELECT DAYOFMONTH(&#39;2011-11-11 11:11:11&#39;)-&gt;11</code></td>
</tr>
<tr>
<td>DAYOFWEEK(d)</td>
<td>日期 d 今天是星期几，1 星期日，2 星期一，以此类推</td>
<td><code>SELECT DAYOFWEEK(&#39;2011-11-11 11:11:11&#39;)-&gt;6</code></td>
</tr>
<tr>
<td>DAYOFYEAR(d)</td>
<td>计算日期 d 是本年的第几天</td>
<td><code>SELECT DAYOFYEAR(&#39;2011-11-11 11:11:11&#39;)-&gt;315</code></td>
</tr>
<tr>
<td>EXTRACT(type FROM d)</td>
<td>从日期 d 中获取指定的值，type 指定返回的值。 type可取值为： MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH</td>
<td><code>SELECT EXTRACT(MINUTE FROM &#39;2011-11-11 11:11:11&#39;) -&gt; 11</code></td>
</tr>
<tr>
<td>ROM_DAYS(n)</td>
<td>计算从 0000 年 1 月 1 日开始 n 天后的日期</td>
<td><code>SELECT FROM_DAYS(1111)-&gt; 0003-01-16</code></td>
</tr>
<tr>
<td>HOUR(t)</td>
<td>返回 t 中的小时值</td>
<td><code>SELECT HOUR(&#39;1:2:3&#39;)-&gt; 1</code></td>
</tr>
<tr>
<td>LAST_DAY(d)</td>
<td>返回给给定日期的那一月份的最后一天</td>
<td><code>SELECT LAST_DAY(&quot;2017-06-20&quot;);-&gt; 2017-06-30</code></td>
</tr>
<tr>
<td>LOCALTIME()</td>
<td>返回当前日期和时间</td>
<td><code>SELECT LOCALTIME()-&gt; 2018-09-19 20:57:43</code></td>
</tr>
<tr>
<td>LOCALTIMESTAMP()</td>
<td>返回当前日期和时间</td>
<td><code>SELECT LOCALTIMESTAMP()-&gt; 2018-09-19 20:57:43</code></td>
</tr>
<tr>
<td>MAKEDATE(year, day-of-year)</td>
<td>基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期</td>
<td><code>SELECT MAKEDATE(2017, 3);-&gt; 2017-01-03</code></td>
</tr>
<tr>
<td>MAKETIME(hour, minute, second)</td>
<td>组合时间，参数分别为小时、分钟、秒</td>
<td><code>SELECT MAKETIME(11, 35, 4);-&gt; 11:35:04</code></td>
</tr>
<tr>
<td>MICROSECOND(date)</td>
<td>返回日期参数所对应的毫秒数</td>
<td><code>SELECT MICROSECOND(&quot;2017-06-20 09:34:00.000023&quot;);-&gt; 23</code></td>
</tr>
<tr>
<td>MINUTE(t)</td>
<td>返回 t 中的分钟值</td>
<td><code>SELECT MINUTE(&#39;1:2:3&#39;)-&gt; 2</code></td>
</tr>
<tr>
<td>MONTHNAME(d)</td>
<td>返回日期当中的月份名称，如 Janyary</td>
<td><code>SELECT MONTHNAME(&#39;2011-11-11 11:11:11&#39;)-&gt; November</code></td>
</tr>
<tr>
<td>MONTH(d)</td>
<td>返回日期d中的月份值，1 到 12</td>
<td><code>SELECT MONTH(&#39;2011-11-11 11:11:11&#39;)-&gt;11</code></td>
</tr>
<tr>
<td>NOW()</td>
<td>返回当前日期和时间</td>
<td><code>SELECT NOW()-&gt; 2018-09-19 20:57:43</code></td>
</tr>
<tr>
<td>PERIOD_ADD(period, number)</td>
<td>为 年-月 组合日期添加一个时段</td>
<td><code>SELECT PERIOD_ADD(201703, 5);   -&gt; 201708</code></td>
</tr>
<tr>
<td>PERIOD_DIFF(period1, period2)</td>
<td>返回两个时段之间的月份差值</td>
<td><code>SELECT PERIOD_DIFF(201710, 201703);-&gt; 7</code></td>
</tr>
<tr>
<td>QUARTER(d)</td>
<td>返回日期d是第几季节，返回 1 到 4</td>
<td><code>SELECT QUARTER(&#39;2011-11-11 11:11:11&#39;)-&gt; 4</code></td>
</tr>
<tr>
<td>SECOND(t)</td>
<td>返回 t 中的秒钟值</td>
<td><code>SELECT SECOND(&#39;1:2:3&#39;)-&gt; 3</code></td>
</tr>
<tr>
<td>SEC_TO_TIME(s)</td>
<td>将以秒为单位的时间 s 转换为时分秒的格式</td>
<td><code>SELECT SEC_TO_TIME(4320)-&gt; 01:12:00</code></td>
</tr>
<tr>
<td>STR_TO_DATE(string, format_mask)</td>
<td>将字符串转变为日期</td>
<td><code>SELECT STR_TO_DATE(&quot;August 10 2017&quot;, &quot;%M %d %Y&quot;);-&gt; 2017-08-10</code></td>
</tr>
<tr>
<td>SUBDATE(d,n)</td>
<td>日期 d 减去 n 天后的日期</td>
<td><code>SELECT SUBDATE(&#39;2011-11-11 11:11:11&#39;, 1)-&gt;2011-11-10 11:11:11 (默认是天)</code></td>
</tr>
<tr>
<td>SUBTIME(t,n)</td>
<td>时间 t 减去 n 秒的时间</td>
<td><code>SELECT SUBTIME(&#39;2011-11-11 11:11:11&#39;, 5)-&gt;2011-11-11 11:11:06 (秒)</code></td>
</tr>
<tr>
<td>SYSDATE()</td>
<td>返回当前日期和时间</td>
<td><code>SELECT SYSDATE()-&gt; 2018-09-19 20:57:43</code></td>
</tr>
<tr>
<td>TIME(expression)</td>
<td>提取传入表达式的时间部分</td>
<td><code>SELECT TIME(&quot;19:30:10&quot;);-&gt; 19:30:10</code></td>
</tr>
<tr>
<td>TIME_FORMAT(t,f)</td>
<td>按表达式 f 的要求显示时间 t</td>
<td><code>SELECT TIME_FORMAT(&#39;11:11:11&#39;,&#39;%r&#39;)11:11:11 AM</code></td>
</tr>
<tr>
<td>TIME_TO_SEC(t)</td>
<td>将时间 t 转换为秒</td>
<td><code>SELECT TIME_TO_SEC(&#39;1:12:00&#39;)-&gt; 4320</code></td>
</tr>
<tr>
<td>TIMEDIFF(time1, time2)</td>
<td>计算时间差值</td>
<td><code>SELECT TIMEDIFF(&quot;13:10:11&quot;, &quot;13:10:10&quot;);-&gt; 00:00:01</code></td>
</tr>
<tr>
<td>TIMESTAMP(expression, interval)</td>
<td>单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和</td>
<td><code>SELECT TIMESTAMP(&quot;2017-07-23&quot;,  &quot;13:10:11&quot;);-&gt; 2017-07-23 13:10:11</code></td>
</tr>
<tr>
<td>TO_DAYS(d)</td>
<td>计算日期 d 距离 0000 年 1 月 1 日的天数</td>
<td><code>SELECT TO_DAYS(&#39;0001-01-01 01:01:01&#39;)-&gt; 366</code></td>
</tr>
<tr>
<td>WEEK(d)</td>
<td>计算日期 d 是本年的第几个星期，范围是 0 到 53</td>
<td><code>SELECT WEEK(&#39;2011-11-11 11:11:11&#39;)-&gt; 45</code></td>
</tr>
<tr>
<td>WEEKDAY(d)</td>
<td>日期 d 是星期几，0 表示星期一，1 表示星期二</td>
<td><code>SELECT WEEKDAY(&quot;2017-06-15&quot;);-&gt; 3</code></td>
</tr>
<tr>
<td>WEEKOFYEAR(d)</td>
<td>计算日期 d 是本年的第几个星期，范围是 0 到 53</td>
<td><code>SELECT WEEKOFYEAR(&#39;2011-11-11 11:11:11&#39;)-&gt; 45</code></td>
</tr>
<tr>
<td>YEAR(d)</td>
<td>返回年份</td>
<td><code>SELECT YEAR(&quot;2017-06-15&quot;);-&gt; 2017</code></td>
</tr>
<tr>
<td>YEARWEEK(date, mode)</td>
<td>返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推</td>
<td><code>SELECT YEARWEEK(&quot;2017-06-15&quot;);-&gt; 201724</code></td>
</tr>
</tbody>
</table>
<hr>
<h2 id="15-4、MySQL-高级函数"><a href="#15-4、MySQL-高级函数" class="headerlink" title="15.4、MySQL 高级函数"></a>15.4、MySQL 高级函数</h2><table>
<thead>
<tr>
<th>函数名</th>
<th>描述</th>
<th>实例</th>
</tr>
</thead>
<tbody>
<tr>
<td>BIN(x)</td>
<td>返回 x 的二进制编码</td>
<td>15 的 2 进制编码:<code>SELECT BIN(15); -- 1111</code></td>
</tr>
<tr>
<td>BINARY(s)</td>
<td>将字符串 s 转换为二进制字符串</td>
<td><code>SELECT BINARY &quot;test1&quot;;-&gt; test1</code></td>
</tr>
<tr>
<td><code>CASE expression    WHEN condition1 THEN result1    WHEN condition2 THEN result2   ...    WHEN conditionN THEN resultN    ELSE resultEND</code></td>
<td>CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。</td>
<td><code>SELECT CASE 　　WHEN 1 &gt; 0　　THEN &#39;1 &gt; 0&#39;　　WHEN 2 &gt; 0　　THEN &#39;2 &gt; 0&#39;　　ELSE &#39;3 &gt; 0&#39;　　END-&gt;1 &gt; 0</code></td>
</tr>
<tr>
<td>CAST(x AS type)</td>
<td>转换数据类型</td>
<td>字符串日期转换为日期：<code>SELECT CAST(&quot;2017-08-29&quot; AS DATE);-&gt; 2017-08-29</code></td>
</tr>
<tr>
<td>COALESCE(expr1, expr2, …., expr_n)</td>
<td>返回参数中的第一个非空表达式（从左向右）</td>
<td><code>SELECT COALESCE(NULL, NULL, NULL, &#39;test1.com&#39;, NULL, &#39;google.com&#39;);-&gt; test1.com</code></td>
</tr>
<tr>
<td>CONNECTION_ID()</td>
<td>返回服务器的连接数</td>
<td><code>SELECT CONNECTION_ID();-&gt; 4292835</code></td>
</tr>
<tr>
<td>CONV(x,f1,f2)</td>
<td>返回 f1 进制数变成 f2 进制数</td>
<td><code>SELECT CONV(15, 10, 2);-&gt; 1111</code></td>
</tr>
<tr>
<td>CONVERT(s USING cs)</td>
<td>函数将字符串 s 的字符集变成 cs</td>
<td><code>SELECT CHARSET(&#39;ABC&#39;)-&gt;utf-8    SELECT CHARSET(CONVERT(&#39;ABC&#39; USING gbk))-&gt;gbk</code></td>
</tr>
<tr>
<td>CURRENT_USER()</td>
<td>返回当前用户</td>
<td><code>SELECT CURRENT_USER();-&gt; guest@%</code></td>
</tr>
<tr>
<td>DATABASE()</td>
<td>返回当前数据库名</td>
<td><code>SELECT DATABASE();   -&gt; test1</code></td>
</tr>
<tr>
<td>IF(expr,v1,v2)</td>
<td>如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。</td>
<td><code>SELECT IF(1 &gt; 0,&#39;正确&#39;,&#39;错误&#39;)    -&gt;正确</code></td>
</tr>
<tr>
<td><a href="http://www.test1.com/mysql/mysql-func-ifnull.html" target="_blank" rel="noopener">IFNULL(v1,v2)</a></td>
<td>如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。</td>
<td><code>SELECT IFNULL(null,&#39;Hello Word&#39;)-&gt;Hello Word</code></td>
</tr>
<tr>
<td>ISNULL(expression)</td>
<td>判断表达式是否为空</td>
<td><code>SELECT ISNULL(NULL);-&gt;1</code></td>
</tr>
<tr>
<td>LAST_INSERT_ID()</td>
<td>返回最近生成的 AUTO_INCREMENT 值</td>
<td><code>SELECT LAST_INSERT_ID();-&gt;6</code></td>
</tr>
<tr>
<td>NULLIF(expr1, expr2)</td>
<td>比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1</td>
<td><code>SELECT NULLIF(25, 25);-&gt;</code></td>
</tr>
<tr>
<td>SESSION_USER()</td>
<td>返回当前用户</td>
<td><code>SELECT SESSION_USER();-&gt; guest@%</code></td>
</tr>
<tr>
<td>SYSTEM_USER()</td>
<td>返回当前用户</td>
<td><code>SELECT SYSTEM_USER();-&gt; guest@%</code></td>
</tr>
<tr>
<td>USER()</td>
<td>返回当前用户</td>
<td><code>SELECT USER();-&gt; guest@%</code></td>
</tr>
<tr>
<td>VERSION()</td>
<td>返回数据库的版本号</td>
<td><code>SELECT VERSION()-&gt; 5.6.34</code></td>
</tr>
</tbody>
</table>
<h1 id="16、MySQL-索引"><a href="#16、MySQL-索引" class="headerlink" title="16、MySQL 索引"></a>16、MySQL 索引</h1><p>MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。</p>
<p>索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。</p>
<p>创建索引时，你需要确保该索引是应用在    SQL 查询语句的条件(一般作为 WHERE 子句的条件)。</p>
<p>实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。</p>
<p>上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。</p>
<p>建立索引会占用磁盘空间的索引文件。</p>
<h2 id="16-1、普通索引"><a href="#16-1、普通索引" class="headerlink" title="16.1、普通索引"></a>16.1、普通索引</h2><h3 id="16-1-1、创建索引"><a href="#16-1-1、创建索引" class="headerlink" title="16.1.1、创建索引"></a>16.1.1、创建索引</h3><p>这是最基本的索引，它没有任何限制。它有以下几种创建方式：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">CREATE INDEX indexName ON mytable(username(length)); </span><br><span class="line"></span><br><span class="line">//创建索引</span><br><span class="line"> create index id on B(A_ID);</span><br></pre></td></tr></table></figure>
<p>如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。</p>
<h3 id="16-1-2、修改表结构-添加索引"><a href="#16-1-2、修改表结构-添加索引" class="headerlink" title="16.1.2、修改表结构(添加索引)"></a>16.1.2、修改表结构(添加索引)</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER table tableName ADD INDEX indexName(columnName)</span><br></pre></td></tr></table></figure>
<h3 id="16-1-3、创建表的时候直接指定"><a href="#16-1-3、创建表的时候直接指定" class="headerlink" title="16.1.3、创建表的时候直接指定"></a>16.1.3、创建表的时候直接指定</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE mytable(  </span><br><span class="line"> </span><br><span class="line">ID INT NOT NULL,   </span><br><span class="line"> </span><br><span class="line">username VARCHAR(16) NOT NULL,  </span><br><span class="line"> </span><br><span class="line">INDEX [indexName] (username(length))  </span><br><span class="line"> </span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<h3 id="16-1-4、删除索引的语法"><a href="#16-1-4、删除索引的语法" class="headerlink" title="16.1.4、删除索引的语法"></a>16.1.4、删除索引的语法</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DROP INDEX [indexName] ON mytable;</span><br></pre></td></tr></table></figure>
<h2 id="16-2、唯一索引"><a href="#16-2、唯一索引" class="headerlink" title="16.2、唯一索引"></a>16.2、唯一索引</h2><p>它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：</p>
<h3 id="16-2-1、创建索引"><a href="#16-2-1、创建索引" class="headerlink" title="16.2.1、创建索引"></a>16.2.1、创建索引</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CREATE UNIQUE INDEX indexName ON mytable(username(length))</span><br></pre></td></tr></table></figure>
<h3 id="16-2-2、修改表结构"><a href="#16-2-2、修改表结构" class="headerlink" title="16.2.2、修改表结构"></a>16.2.2、修改表结构</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER table mytable ADD UNIQUE [indexName] (username(length))</span><br></pre></td></tr></table></figure>
<h3 id="16-2-3、创建表的时候直接指定"><a href="#16-2-3、创建表的时候直接指定" class="headerlink" title="16.2.3、创建表的时候直接指定"></a>16.2.3、创建表的时候直接指定</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE mytable(  </span><br><span class="line"> </span><br><span class="line">ID INT NOT NULL,   </span><br><span class="line"> </span><br><span class="line">username VARCHAR(16) NOT NULL,  </span><br><span class="line"> </span><br><span class="line">UNIQUE [indexName] (username(length))  </span><br><span class="line"> </span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<h2 id="16-3、使用ALTER-命令添加和删除索引"><a href="#16-3、使用ALTER-命令添加和删除索引" class="headerlink" title="16.3、使用ALTER 命令添加和删除索引"></a>16.3、使用ALTER 命令添加和删除索引</h2><p>有四种方式来添加数据表的索引：</p>
<ul>
<li><strong>ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):</strong> 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。</li>
<li><strong>ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):</strong> 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。</li>
<li><strong>ALTER TABLE tbl_name ADD INDEX index_name (column_list):</strong> 添加普通索引，索引值可出现多次。</li>
<li><strong>ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):</strong>该语句指定了索引为 FULLTEXT ，用于全文索引。</li>
</ul>
<p>以下实例为在表中添加索引。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; ALTER TABLE testalter_tbl ADD INDEX (c);</span><br></pre></td></tr></table></figure>
<p>你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; ALTER TABLE testalter_tbl DROP INDEX c;</span><br></pre></td></tr></table></figure>
<h2 id="16-4、使用-ALTER-命令添加和删除主键"><a href="#16-4、使用-ALTER-命令添加和删除主键" class="headerlink" title="16.4、使用 ALTER 命令添加和删除主键"></a>16.4、使用 ALTER 命令添加和删除主键</h2><p>主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; ALTER TABLE testalter_tbl MODIFY test1 INT NOT NULL;</span><br><span class="line">mysql&gt; ALTER TABLE testalter_tbl ADD PRIMARY KEY (test1);</span><br></pre></td></tr></table></figure>
<p>你也可以使用 ALTER 命令删除主键：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; ALTER TABLE testalter_tbl DROP PRIMARY KEY;</span><br></pre></td></tr></table></figure>
<p>删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。</p>
<h2 id="16-5、显示索引信息"><a href="#16-5、显示索引信息" class="headerlink" title="16.5、显示索引信息"></a>16.5、显示索引信息</h2><p>你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。</p>
<p>尝试以下实例:</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SHOW INDEX FROM table_name; \G</span><br></pre></td></tr></table></figure>
<p>mysql&gt; show index from B;<br>+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+<br>| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |<br>+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+<br>| B     |          0 | PRIMARY  |            1 | A_ID        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |<br>+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+</p>
<h1 id="17、MySQL-事务"><a href="#17、MySQL-事务" class="headerlink" title="17、MySQL 事务"></a>17、MySQL 事务</h1><p>​    MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！</p>
<ul>
<li>在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。</li>
<li>事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。</li>
<li>事务用来管理 insert,update,delete 语句</li>
</ul>
<p>一般来说，事务是必须满足4个条件（ACID）：：原子性（<strong>A</strong>tomicity，或称不可分割性）、一致性（<strong>C</strong>onsistency）、隔离性（<strong>I</strong>solation，又称独立性）、持久性（<strong>D</strong>urability）。</p>
<ul>
<li><strong>原子性：</strong>一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，在中间某个环节不会结束。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。</li>
<li><strong>一致性：</strong>在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。</li>
<li><strong>隔离性：</strong>数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。</li>
<li><strong>持久性：</strong>事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。</li>
</ul>
<h3 id="17-1、事务控制语句："><a href="#17-1、事务控制语句：" class="headerlink" title="17.1、事务控制语句："></a>17.1、事务控制语句：</h3><ul>
<li>BEGIN或START TRANSACTION；显式地开启一个事务；</li>
<li>COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；</li>
<li>ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；</li>
<li>SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；</li>
<li>RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；</li>
<li>ROLLBACK TO identifier；把事务回滚到标记点；</li>
<li>SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。</li>
</ul>
<h3 id="17-2、MYSQL-事务处理主要有两种方法："><a href="#17-2、MYSQL-事务处理主要有两种方法：" class="headerlink" title="17.2、MYSQL 事务处理主要有两种方法："></a>17.2、MYSQL 事务处理主要有两种方法：</h3><p>1、用 BEGIN, ROLLBACK, COMMIT来实现</p>
<ul>
<li><strong>BEGIN</strong> 开始一个事务</li>
<li><strong>ROLLBACK</strong> 事务回滚</li>
<li><strong>COMMIT</strong> 事务确认</li>
</ul>
<p>2、直接用 SET 来改变 MySQL 的自动提交模式:</p>
<ul>
<li><strong>SET AUTOCOMMIT=0</strong> 禁止自动提交</li>
<li><strong>SET AUTOCOMMIT=1</strong> 开启自动提交</li>
</ul>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2019/01/14/mysql/sql_数据库基础/" data-id="ckcc5iwq5009lq4uwdfv7po8s" class="article-share-link">Teilen</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/">mysql</a></li></ul>

    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2019/01/14/mysql/sql_数据库进阶/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Neuer</strong>
      <div class="article-nav-title">
        
          数据库-进阶
        
      </div>
    </a>
  
  
    <a href="/2019/01/11/vue-select/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Älter</strong>
      <div class="article-nav-title">vue-select</div>
    </a>
  
</nav>

  
</article>

</section>
        
          <aside id="sidebar">
  
    

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tags</h3>
    <div class="widget">
      <ul class="tag-list"><li class="tag-list-item"><a class="tag-list-link" href="/tags/Design-Patterns/">Design Patterns</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Git/">Git</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Html/">Html</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Http/">Http</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/JavaScript/">JavaScript</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Sort/">Sort</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/algorithm/">algorithm</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/ali-weixin-pay/">ali/weixin pay</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/docker/">docker</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/elasticsearch/">elasticsearch</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/go/">go</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/hexo/">hexo</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/idea/">idea</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/java/">java</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/jvm/">jvm</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/linux/">linux</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/logstash/">logstash</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mysql/">mysql</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/php/">php</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/pytest/">pytest</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/python/">python</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/springboot/">springboot</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/util/">util</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/vue/">vue</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/wechat/">wechat</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/我的世界/">我的世界</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/消息队列/">消息队列</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/线程/">线程</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tag Cloud</h3>
    <div class="widget tagcloud">
      <a href="/tags/Design-Patterns/" style="font-size: 16.36px;">Design Patterns</a> <a href="/tags/Git/" style="font-size: 10px;">Git</a> <a href="/tags/Html/" style="font-size: 10px;">Html</a> <a href="/tags/Http/" style="font-size: 12.73px;">Http</a> <a href="/tags/JavaScript/" style="font-size: 15.45px;">JavaScript</a> <a href="/tags/Sort/" style="font-size: 18.18px;">Sort</a> <a href="/tags/algorithm/" style="font-size: 11.82px;">algorithm</a> <a href="/tags/ali-weixin-pay/" style="font-size: 13.64px;">ali/weixin pay</a> <a href="/tags/docker/" style="font-size: 17.27px;">docker</a> <a href="/tags/elasticsearch/" style="font-size: 10px;">elasticsearch</a> <a href="/tags/go/" style="font-size: 11.82px;">go</a> <a href="/tags/hexo/" style="font-size: 10px;">hexo</a> <a href="/tags/idea/" style="font-size: 10px;">idea</a> <a href="/tags/java/" style="font-size: 20px;">java</a> <a href="/tags/jvm/" style="font-size: 11.82px;">jvm</a> <a href="/tags/linux/" style="font-size: 18.18px;">linux</a> <a href="/tags/logstash/" style="font-size: 12.73px;">logstash</a> <a href="/tags/mysql/" style="font-size: 14.55px;">mysql</a> <a href="/tags/php/" style="font-size: 11.82px;">php</a> <a href="/tags/pytest/" style="font-size: 10px;">pytest</a> <a href="/tags/python/" style="font-size: 13.64px;">python</a> <a href="/tags/springboot/" style="font-size: 17.27px;">springboot</a> <a href="/tags/util/" style="font-size: 19.09px;">util</a> <a href="/tags/vue/" style="font-size: 10px;">vue</a> <a href="/tags/wechat/" style="font-size: 10.91px;">wechat</a> <a href="/tags/我的世界/" style="font-size: 10px;">我的世界</a> <a href="/tags/消息队列/" style="font-size: 10.91px;">消息队列</a> <a href="/tags/线程/" style="font-size: 11.82px;">线程</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archiv</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/05/">May 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/04/">April 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/03/">March 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/01/">January 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/10/">October 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/08/">August 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/06/">June 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/04/">April 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/03/">March 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/02/">February 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/01/">January 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/12/">December 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/11/">November 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/10/">October 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/09/">September 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/07/">July 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/06/">June 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/05/">May 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/04/">April 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/03/">March 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/02/">February 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/01/">January 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/12/">December 2017</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">letzter Beitrag</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2020/05/17/linux/linux_shell/">linux—shell</a>
          </li>
        
          <li>
            <a href="/2020/05/16/linux/linux_awk_sed/">linux-awk\sed 示例</a>
          </li>
        
          <li>
            <a href="/2020/04/11/linux/linux_bash/">linux—bash</a>
          </li>
        
          <li>
            <a href="/2020/03/29/hello-world/">Hello World</a>
          </li>
        
          <li>
            <a href="/2020/03/16/go基础/">go基础知识</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy; 2020 vchen<br>
      Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>
    </div>
    <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    

<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>


  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
  <script src="/fancybox/jquery.fancybox.pack.js"></script>


<script src="/js/script.js"></script>



  </div>
</body>
</html>